在一个in子句中使用逗号分隔的参数。

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:

我将“param1、param2、parma3”作为varchar参数从SSRS传递到存储过程:我需要在查询的in子句中使用它，但是需要先改变它的格式，比如:

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

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

在不创建函数和参数表的情况下重新格式化参数的最佳方式是什么?

5 个解决方案

#1

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

试试这个，只需在@params字符串的开头和结尾添加逗号。

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

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

SQL FIDDLE

SQL小提琴

#2

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

您可以使用split函数并按照以下方式使用我的split fnSplitString返回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

#3

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

将Params加载到字符串中，并作为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

#4

"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:

“最佳方式”是有争议的，但有一种经典的方法仍然没有“创建函数和表参数”，那就是在存储过程中使用动态SQL:

-- 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:

不过，这有几个假设:

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.
在值列表中的逗号后面是空格。当然，这个解决方案的变化可以解决这方面的偏差，但是注意这个假设是很重要的。
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.
逗号分隔的值本身并不包含逗号——这不太可能，但值得一提，因为值是否会满足这个约束有时会被忽略。

#5

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

如果您正在使用SQL 2016和以上的string_split，您可以使用它。

-- @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

更多关于string_split check offical文档管理的信息

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

此外，TRIM()用于从空白区域中修剪值。

#1