SQL Server（2008）传递ArrayList或字符串到SP for IN（） [英] SQL Server (2008) Pass ArrayList or String to SP for IN()

问题描述

我想知道如何将一个ArrayList，List < int >或StringBuilder逗号分隔的列表传递给一个存储过程，使我找到一个ID列表IN（）：

  @myList varchar（50）
 
 SELECT * 
 FROM tbl 
 WHERE ID IN（@myList）
  

在C＃ string是逗号分隔的;但是当使用nvarchar（50）例如，作为存储过程中的参数的类型 - 我得到一个错误，因为它不能将'1,2,3'转换为int它期望在IN（）之间。 / p>

任何想法？非常感激。


Pete

解决方案

您可以使用用户自定义函数，例如

  CREATE函数[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 
 
如果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 
  

它接受一个以逗号分隔的nvarchar逗号分隔的列表，并返回一个包含这些id的表作为int。然后可以在存储过程中返回的表上加入如下 -

  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 
  

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 Server（2008）传递ArrayList或字符串到SP for IN（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！