SQL服务器(2008)通的ArrayList或者字符串为SP IN() [英] SQL Server (2008) Pass ArrayList or String to SP for IN()
问题描述
我不知道我怎样才能通过其中一个ArrayList中,列表< INT
>或StringBuilder的逗号分隔的列表存储过程这样的,我觉得用ID列表IN():
@myList VARCHAR(50)选择 *
FROM TBL
其中id IN(@myList)
在C#我目前正在建设的列表作为一个字符串,它是逗号delimeted;然而,当使用例如为nvarchar(50),作为在存储过程中参数的类型 - 因为它不能将'1,2,3'为int它的IN之间的预期()我得到一个错误<。 / p>
任何想法?大部分AP preciated。
皮特
您可以使用一个用户定义函数,如
CREATE函数[DBO]。[csl_to_table](@list为nvarchar(MAX))
退货@list_table表([ID] INT)
如
开始
DECLARE @index INT,
@start_index INT,
@id INT 选择@index = 1
选择@start_index = 1
WHILE @index&LT; = DATALENGTH(@list)
开始 IF SUBSTRING(@列表,@指数,1)=','
\t开始 SELECT @id = CAST(SUBSTRING(@list,@start_index,@index - @start_index)AS INT)
INSERT @list_table([ID])VALUES(@id)
SELECT @start_index = @index + 1
\t结束
SELECT @index = @index + 1
结束
SELECT @id = CAST(SUBSTRING(@list,@start_index,@index - @start_index)AS INT)
INSERT @list_table([ID])VALUES(@id)
返回
结束
它接受逗号分隔的ID列表,并返回这些ID作为整数的表一个nvarchar。然后,您可以在返回的表中加入您的存储过程像这样 -
定义@passed_in_ids表(ID INT)INSERT INTO @passed_in_ids(ID)
选择
ID
从
[DBO]。[csl_to_table](@your_passed_in_csl)选择 *
从
为myTable
内部联接
@passed_in_ids IDS
上
myTable.id = ids.id
I was wondering how I can pass either an ArrayList, List<int
> or StringBuilder comma delimited list to a stored procedure such that I find a list of IDs using IN():
@myList varchar(50)
SELECT *
FROM tbl
WHERE Id IN (@myList)
In C# I am currently building the list as a string which is comma delimeted; however when using nvarchar(50) for example, as the type for the param in the stored procedure - I get an error as it can't convert '1,2,3' to int which it expects between the IN().
Any ideas? Much appreciated.
Pete
You could use a User Defined function such as
CREATE function [dbo].[csl_to_table] ( @list nvarchar(MAX) )
RETURNS @list_table TABLE ([id] INT)
AS
BEGIN
DECLARE @index INT,
@start_index INT,
@id INT
SELECT @index = 1
SELECT @start_index = 1
WHILE @index <= DATALENGTH(@list)
BEGIN
IF SUBSTRING(@list,@index,1) = ','
BEGIN
SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
INSERT @list_table ([id]) VALUES (@id)
SELECT @start_index = @index + 1
END
SELECT @index = @index + 1
END
SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
INSERT @list_table ([id]) VALUES (@id)
RETURN
END
Which accepts an nvarchar comma separated list of ids and returns a table of those ids as ints. You can then join on the returned table in your stored procedure like so -
DECLARE @passed_in_ids TABLE (id INT)
INSERT INTO @passed_in_ids (id)
SELECT
id
FROM
[dbo].[csl_to_table] (@your_passed_in_csl)
SELECT *
FROM
myTable
INNER JOIN
@passed_in_ids ids
ON
myTable.id = ids.id
这篇关于SQL服务器(2008)通的ArrayList或者字符串为SP IN()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!