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?

Answer 1

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)