Does sp_executesql support multiple values in one parameter and return multiple records?

Question

I have created a stored procedure as shown below, but it's returning only one row instead of 3:

CREATE PROCEDURE [dbo].[tempsp] 
    (@RecycleIds NVARCHAR(MAX) = NULL)  
AS  
BEGIN  
    DECLARE @Err INT  
    DECLARE @WhereClause NVARCHAR(MAX)  
    DECLARE @SQLText1 NVARCHAR(MAX)  
    DECLARE @SQLText NVARCHAR(MAX)
  
    SET @SQLText1 = 'SELECT FROM dbo.SKU '  
  
    IF @RecycledSkuIds IS NOT NULL  
    BEGIN      
        SET @SQLText = 'SELECT FROM dbo.SKU WHERE SKU.SkuId IN (@RecycleIds)'

        EXEC sp_executesql @SQLText, N'@RecycleSkuIds nvarchar', @RecycleIds 
    END   
    ELSE  
    BEGIN  
        EXEC(@SQLText1)
    END           
  
    SET @Err = @@ERROR  
    RETURN @Err  
END  
-------end of stored procedure--------


EXEC tempsp @RecycleIds = '5,6,7'

After running this SQL statement, it only returns one row instead of 3, with the id's of 5, 6, 7.

Can anyone tell me what I am doing wrong? i wanted to use sp_executesql, so that it can be safe against sql injection with strong type defined.

Answer 1

I was trying to retrive the rows whose id matches within the IN clause.

SET @INClauseIds='''' + replace(@Ids, ',', ''',''') + ''''  

Above statement would convert the ID's ='1,2,3' to '1','2','3' which i can directly place in the IN clause.

SET @SQLText1 ='EXEC(''SELECT  Name,SEOFriendlyName FROM SKU Where Id IN ( ''+ @Ids+'' ) )'             

 EXEC sp_executesql @SQLText1 ,N'@INClauseIds nvarchar(max)',@Ids=@INClauseIds 

If you want to avoid the usage of Temp Table which would add extra caliculation time. you can you the above strategy to retrive n number of records. Safe with strongly coupled with sp_executesql and without any sql injection.

Answer 2

You cannot use IN. Or, more accurately, you have a string and you are confusing it with a list. One method is to instead use LIKE:

SET @SQLText = '
SELECT *
FROM dbo.SKU
WHERE CONCAT('','', @RecycleIds, '','') LIKE CONCAT(''%,'', SKU.SkuId, '',%'')
';

Answer 3

Use a table type parameter, with a strongly typed column:

CREATE TYPE dbo.IDs AS table (ID int);
GO

CREATE PROCEDURE [dbo].[tempsp] @RecycleIds dbo.IDs READONLY AS
BEGIN

    IF EXISTS (SELECT 1 FROM @RecycleIds)
        SELECT * --Replace with needed columns
        FROM dbo.SKU S
        --Using EXISTS in case someone silly puts in the same ID twice.
        WHERE EXISTS (SELECT 1
                      FROM @RecycleIds R
                      WHERE R.ID = S.SkuID);
    ELSE
        SELECT * --Replace with needed columns
        FROM dbo.SKU S
END;
GO

Then you could execute it like so:

EXEC dbo.tempsp; --All Rows
GO
DECLARE @RecycleIds dbo.IDs;
INSERT INTO @RecycleIds
VALUES(1),(40),(182);

EXEC dbo.tempsp @RecycleIds;