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

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

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.

Solution

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.