Using a comma-separated parameter in an IN clause

Question

I have 'param1, param2, parma3' coming from SSRS to a stored procedure as a varchar parameter: I need to use it in a query's IN clause but then need to change its format like this first:

select *
from table1
where col1 in('param1', 'param2', 'param3')

What is the best way to reformat the parameter without creating functions and parameter tables?

Leviathan

Parse it in SP and create temp table. – Zamarripa

possible duplicate of Passing a varchar full of comma delimited values to a SQL Server IN function – Cephalization

Answer 1

Try this one, Just need to add commas at the beginning and at the end of @params string.

Declare @params varchar(100) Set @params = ',param1,param2,param3,'

Select * from t
where CHARINDEX(','+cast(col1 as varchar(8000))+',', @params) > 0

SQL FIDDLE

Outrun

Heads up that this yields Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ','. with a red squigly under the comma following ',param1' when I execute it. – Mosera

Answer 2

you can use split function and use it as in following way here my split fnSplitString return splitdata

select * from tb1 where id in(select splitdata from dbo.fnSplitString((select col1 from tb12 where id=3),','))


create FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Haftarah

The OP's question specifed, "what is the best way to reformat the parameter without creating functions and parameter tables". Given that, I assume they were aware of this approach, but didn't want to create an e.g. fnSplitString function.That's also what sets this question apart from other similar questions. – Franklin

Answer 3

If you are using SQL 2016 and above string_split you can use.

-- @param is where you keep your comma separated values example: 
declare @param = 'param1,param2,param3'
select * from table1 where col1 in (select TRIM(value) from string_split(@param,',')

More information about string_split check offical documemt

Furthermore, TRIM() is used to trim values from white spaces.

Stokehold

Answer 4

We can use STRING_SPLIT() in SQL SERVER

DECLARE @params varchar(max)= 'param1,param2,param3'

SELECT *
FROM table1
WHERE col1 IN (SELECT value FROM STRING_SPLIT( @params , ','))

Secession

Answer 5

"Best way" is arguable, but one classic approach that remains without "creating functions and table parameters" is to simply employ dynamic SQL in the stored procedure:

-- FORNOW: local to act as the SP param and arg
declare @values varchar(100) = 'param1, param2, param3'

-- Add opening and closing single quotes, then quotes around each
-- comma-separated list item.
select @values = '''' + REPLACE(@values, ', ', ''', ''') + ''''

-- FORNOW: for clarity/debugging
print @values
--'param1', 'param2', 'param3'

-- Run the desired query as dynamic SQL.
DECLARE @sql as nvarchar(250);
SET @sql = 'select * from table1 where col1 in (' + @values + ')';

EXEC sp_executesql @sql;

This assumes a couple things, though:

1. That commas in the list of values are followed by a space. Variations on this solution can address deviations in this respect of course, but it is important to be aware of this assumption.
2. That the comma-separated values do not themselves have commas in them – unlikely but worth mentioning since whether values will satisfy this constraint sometimes goes unconsidered.

Mosera

Answer 6

Load the Params into a string and execute as an sql :

declare @param varchar(1000) = 'param1, param2, parma3'
declare @sql varchar(4000)
select @sql = 
'select *
from table1
where col1 in(''' + replace(@param,',',''',''') + ''')'

-- print @sql -- to see what you're going to execute
exec sp_executesql @sql

Australasia

Answer 7

DECLARE @params varchar(max) = '1,2,3,4'

SELECT * FROM table2 WHERE colId IN (SELECT value FROM SPLIT(@params,','))

Base on id we can find.

Roselani

What's the difference between SPLIT() and STRING_SPLIT() used in very similar existing answers? In fact, are you even certain SPLIT() is a function in Microsoft SQL Server? (I don't believe it is.) – Franklin