porque supongo que no tienes clientes cuyo id sea = '1,2'
en realidad sql no lo toma como una lista sino como una variable con ese valor, por lo que tienes que convertirlo explicitamente a una tabla. Mas o menos seria asi:
Código:
ALTER PROCEDURE SP_PRUEBA
@IDS NVARCHAR(1000)
AS
DECLARE @NOMBRES NVARCHAR(1000)
SET @NOMBRES=''
DECLARE @CLIENT_IDS TABLE (CLIENTEID INT)
WHILE PATINDEX('%,%',@IDS)>0
BEGIN
INSERT INTO @CLIENT_IDS (CLIENTEID)
VALUES (ISNULL(LEFT(@IDS,PATINDEX('%,%',@IDS)-1),0))
SET @IDS = RIGHT(@IDS,LEN(@IDS)-PATINDEX('%,%',@IDS))
END
INSERT INTO @CLIENT_IDS(CLIENTEID)
VALUES (ISNULL(@IDS,0))
SELECT @NOMBRES = @NOMBRES + NOMBRE + ','
FROM @CLIENT_IDS I
INNER JOIN
CLIENTE C
ON I.CLIENTEID = C.ID_CLIENTE
SELECT @NOMBRES
GO
Saludos!