SQL - 使用 IN 的带有 Select 语句的存储过程 (@Variable_CommaDelimitedListOfIDS) [英] SQL - Stored Procedure with Select Statement using IN (@Variable_CommaDelimitedListOfIDS)

问题描述

我正在创建一个存储过程，我想将逗号分隔的 Id 列表作为变量传递给该过程.我想将 Id 用于选择语句，例如:

I am creating a stored procedure to which I want to pass as variable a comma delimited list of Ids. I want to use the Ids into a select statement, something like:

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(@Ids)

显然我得到的错误是 @Ids 是一个 varchar 而不是一个 INT 但我如何转换逗号分隔的列表?

Obviously I get the error that @Ids is a varchar and not an INT but how can I convert the comma delimited list?

推荐答案

对于那些情况我使用这个表格功能，您可以根据自己的需要进行调整:

For those cases I use this table function, which you can adapt to your needs:

CREATE FUNCTION dbo.f_params_to_list (@par VARCHAR(500))
returns @result TABLE (value VARCHAR(30))
AS  
begin
     DECLARE @TempList table
          (
          value VARCHAR(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @par = LTRIM(RTRIM(@par))+ ','
     SET @Pos = CHARINDEX(',', @par, 1)

     IF REPLACE(@par, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@par, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @par = RIGHT(@par, LEN(@par) - @Pos)
               SET @Pos = CHARINDEX(',', @par, 1)

          END
     END    
     INSERT @result
     SELECT value 
        FROM @TempList
     RETURN
END    

在您的存储过程中，您会像这样使用它:

In your stored procedure you would use it like this:

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(SELECT value FROM dbo.f_params_to_list(@Ids))

这篇关于SQL - 使用 IN 的带有 Select 语句的存储过程 (@Variable_CommaDelimitedListOfIDS)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！