CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

SQL Server - is the value in the passed in parameter array?

i am tyring to pass in a LIST of values from C# and query it in my stored procedure.

this is what i have:

select   distinct FileStream_ID
FROM         dbo.vEmailSent
WHERE     
RequestType_ID in ( + '"' + @RequestTypeID +'"' + )

but i'm getting an error on syntax with the parenthesis and without them. please help.

Upvotes: 0

Views: 358

Answers (3)

Bridge
Bridge

Reputation: 30691

This won't work, you'll need to use a table valued parameter (if you're on SQL Server 2008+), Dynamic SQL, XML, split the string into a table using a function or similar.

MVP Erland Sommarskog has an excellent breakdown of the positives and negatives of each approach, with performance data here:

http://www.sommarskog.se/arrays-in-sql.html

Upvotes: 0

Curtis
Curtis

Reputation: 103388

You potentially have a few syntax errors.

First of all, you have an and at the start of your WHERE clause. Change to:

WHERE     
RequestType_ID in ( + '"' + @RequestTypeID +'"' + )

Secondly, you have + at the start & end of the IN parenthesis. This is invalid and should be changed to:

WHERE
RequestType_ID in ('"' + @RequestTypeID + '"')

Finally, you shouldn't have '"'. If your parameter @RequestTypeID is an int type, you should CAST() to a varchar type, rather than wrapping in quote marks:

WHERE
RequestType_ID in (CAST(@RequestTypeID as varchar(100))

I suspect this is not what you are trying to do though, and that @RequestTypeID is actually comma-delimited integers.

In which case, you cannot use this like you are attempting. If @RequestTypeID = '1,2,3', then SQL is going to return records where RequestType_ID = '1,2,3'. Therefore 0 results.

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460208

You have an AND directly after the WHERE. But even if you fix that, you need a Split function, then this works:

WHERE
    ([RequestType_ID] IN
        (SELECT  Item
         FROM    dbo.Split(@RequestTypeID, ',') AS  Split))

This is a possible implementation of the table-valued function:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END

Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters

Upvotes: 1

Related Questions