Duplicate of
Dynamic SQL Comma Delimited Value Query
Parameterized Queries with Like and In
我有一个SQL Server存储过程，我想将varchar个完整的逗号分隔值传递给IN函数.例如:
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT *
FROM sometable
WHERE tableid IN (@Ids);
这当然行不通.我得到了一个错误:
将varchar值"1,2,3,5,4,6,7,98234"转换为数据类型int时，转换失败.
我如何在不依赖于构建动态SQL的情况下实现这一点(或类似的事情)？
Don't use a function that loops to split a string!，我下面的函数将非常快地拆分字符串，没有循环！
在使用my函数之前，您需要设置一个"helper"表，每个数据库只需执行一次:
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
使用此函数拆分字符串，它不会循环，而且速度非常快:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000) --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.
Returns a table, one row per item in the list, with a column name "ListValue"
EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')
returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B
(10 row(s) affected)
**/
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
RETURN
END --Function FN_ListToTable
可以将此函数用作联接中的表:
SELECT
Col1, COl2, Col3...
FROM YourTable
INNER JOIN FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue
以下是你的例子:
Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)