sql - 如何将包含列表的变量传递给动态 SQL 查询？

Question

我有一个函数可以将字符串列表转换为整数表:

USE [IFRS_Temp]
GO
/****** Object:  UserDefinedFunction [dbo].[CSVToTable]    Script Date: 01/12/2019 3:36:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
    RETURN
END

---

declare @listOfIDs varchar(1000);
SET @listOfIDs = '5, 6, 7, 8, 9, 15, 28, 31, 49, 51, 59, 61'; 

select id from  [dbo].[CSVToTable] (@listOfIDs) --this code is ok5

结果正确:

6
7
8
9
15
28
31
49
51
59
61

这会引发错误:

exec('select id from  [dbo].[CSVToTable] ('+@listOfIDs+')') -- error

结果:

Procedure or function dbo.CSVToTable has too many arguments specified.

我需要第二个查询，因为我的查询是动态的。谢谢

Answer 1

简单

EXECUTE ('select id from  [dbo].[CSVToTable] ('''+@listOfIDs+''')')
        declare @listOfIDs varchar(1000);

或者，哪种方式更好

SET @listOfIDs = '5, 6, 7, 8, 9, 15, 28, 31, 49, 51, 59, 61'; 

EXECUTE sp_executesql N'select id from  [dbo].[CSVToTable] (@listOfIDs)',
                      N'@listOfIDs VARCHAR(1000)',
                      @listOfIDs;

• 为什么会出现此错误？

  Procedure or function dbo.CSVToTable has too many arguments specified.

因为你真的传递了太多的参数，需要更多的参数来理解这个运行这个查询并看看你真正传递给你的函数的是什么

SELECT 'select id from  [dbo].[CSVToTable] ('+@listOfIDs+')';

这将返回(这是你真正想要执行的)

select id from  [dbo].[CSVToTable] (5, 6, 7, 8, 9, 15, 28, 31, 49, 51, 59, 61)

而不是(这是你需要的)

SELECT 'select id from  [dbo].[CSVToTable] ('''+@listOfIDs+''')';

---

• 好的，但为什么 sp_executesql 比 exec 好？

简单地说，EXEC 会强制您将所有变量连接到一个字符串中，这是最糟糕的事情，这会使您的代码完全开放SQL 注入(inject)。参见 Bad Habits to Kick : Using EXEC() instead of sp_executesql ，这并不意味着 sp_executesql 是 100% 安全的，但它允许在 EXEC() 时对语句进行参数化不会，因此它在 SQL 注入(inject)方面比 EXEC 更安全。

---

最后，由于您标记了 sql-server而且你不指定版本，我建议你使用 SPLIT_STRING() 函数 (2016+) 比你的强，如果你没有 2016+ 版本，而不是创建你自己的而不使用 WHILE 循环以获得更好的性能，因为 WHILE 循环会执行缓慢，因此你应该避免它。

例子:

• How to split a comma-separated value to columns

• Tally OH! An Improved SQL 8K “CSV Splitter” Function

• Reaping the benefits of the Window functions in T-SQL