sql server stored procedure IN parameter

Question

In SQL Server, I am passing String as parameter :

@Param1 = Test,Test1,Test2

I am formatting it to:

@Param1 = 'Test','Test1','Test2'

When I am trying to use this in SELECT statement with IN parameter, it is not returning any data

SELECT * FROM TABLE1 where COLUMN1 IN (@Param1)

What is the correct syntax?

What data type does `@param1` have? The way you format it won't work in the first place. — juergen d, May 08 '14 at 15:46
The `IN` operator expects a **list of items**, e.g. a list of numbers or a list of strings. What you're passing in is a **single string** with commas and stuff - but it's still a **single** string. Therefore - you need to **split up** your string parameter into a **list of strings** before you can pass it into the `IN` operator.... Search this site - there are **HUNDREDS** of questions (and a lot of answers!) for this topic already — marc_s, May 08 '14 at 15:46
You could make `@Param1` a one column table variable and insert those string values as distinct rows into that, then you can rephrase your statement to `SELECT * FROM TABLE1 where COLUMN1 IN (SELECT ColumnName From @Param1)` — DrCopyPaste, May 08 '14 at 15:48

score 0 · Accepted Answer · answered May 08 '14 at 15:56

As pointed out already in the comments by marc_s, IN requires a list of values not simply one variable.

But you could provide those values in a SELECT from a simple table variable like this:

DECLARE @Param1 TABLE (Value NVARCHAR(255));
INSERT INTO @Param1 (Value)
    SELECT 'Test1'
    UNION
    SELECT 'Test2'
    UNION
    SELECT 'Test3'

SELECT
        *
    FROM TABLE1
    WHERE
        COLUMN1 IN
            (SELECT Value FROM @Param1)

Chains · Answer 2 · 2014-05-08T16:08:51.877

I wish there were array vars like that! :-)

But you can convert it to a table using a function, and then use the table with your IN clause as you're attempting.

Here is how that would look:

DECLARE @Param1 nvarchar(max)
SET @Param1 = 'Test,Test1,Test2'

SELECT * 
FROM myTable 
WHERE myField In (Select ParsedString From dbo.ParseStringList(@Param1))

And here is (at least one way to write) the function:

CREATE Function [dbo].[ParseStringList]  (@StringArray nvarchar(max) )  
Returns @tbl_string Table  (ParsedString nvarchar(max))  As  

BEGIN 

DECLARE @end Int,
        @start Int

SET @stringArray =  @StringArray + ',' 
SET @start=1
SET @end=1

WHILE @end<Len(@StringArray)
    BEGIN
        SET @end = CharIndex(',', @StringArray, @end)
        INSERT INTO @tbl_string 
            SELECT
                Substring(@StringArray, @start, @end-@start)

        SET @start=@end+1
        SET @end = @end+1
    END

RETURN
END

sql server stored procedure IN parameter

2 Answers2