本文介绍了SQL - 使用 IN 的带有 Select 语句的存储过程 (@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)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!