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.

If you define your parameter like this: `N'@RecycleSkuIds nvarchar',` - then you get a parameter with a length of **exactly ONE character** which is most likely not what you want. You should really get in the habit of **always** defining an **explicit length** whenever defining a `char(n)/nchar(n)/varchar(n)/nvarchar(n)` column, variable or parameter! — marc_s, May 14 '21 at 11:15

score 4 · Answer 1 · answered May 14 '21 at 11:38

4

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;

answered May 14 '21 at 11:38

Thom A

88,727
11
45
75

Better to also add how to pass the parameter with `sp_executesql`. Also if your table type had a primary key then you wouldn' need an `exists` and it also might perform better – Charlieface May 14 '21 at 13:02
I'm not using `sp_executesql` @Charlieface ... Why would I explain how to use it? There's literally no need to dynamic SQL here. – Thom A May 14 '21 at 13:05
Quite right, but OP *is*, and may need to due to kitchen-sink query – Charlieface May 14 '21 at 13:06
Again, there's no need for dynamic SQL with the question we have, @Charlieface . If that is the question, then the OP should be posting a new question. Though the OP clearly knows how to pass the parameter to `sp_executesql`; they just didn't define the length properly. – Thom A May 14 '21 at 13:07

shiga soumya · Accepted Answer · 2021-06-17T08:42:27.847

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.

This, however, is open to injection and thus should *not* be used. — Thom A, Jun 17 '21 at 08:12

Gordon Linoff · Answer 3 · 2021-05-14T11:48:32.657

-1

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, '',%'')
';

edited May 14 '21 at 11:48

answered May 14 '21 at 10:57

Gordon Linoff

1,242,037
58
646
786

1

No `%` LIKE wildcard needed? – jarlh May 14 '21 at 11:13
1

I would note that this solution won't be SARGable, so for large tables could be far from performant. An index on the column `SkuID` won't be able to be used, and I would not be surprised if that column is the `CLUSTERED INDEX` considering that the table is called `Sku`. – Thom A May 14 '21 at 11:55

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

3 Answers3

Linked