如何将包含列表的变量传递给动态 SQL 查询? [英] How do I pass a variable that contains a list to a dynamic SQL query?

问题描述

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

使用 [IFRS_Temp]走/****** 对象:UserDefinedFunction [dbo].[CSVToTable] 脚本日期:01/12/2019 3:36:10 PM ******/设置 ANSI_NULLS ON走设置 QUOTED_IDENTIFIER ON走更改函数 [dbo].[CSVToTable] (@InStr VARCHAR(MAX))返回@TempTab 表(id int 不为空)作为开始;-- 确保输入以逗号结尾SET @InStr = REPLACE(@InStr + ',', ',,', ',')声明@SP INT声明@VALUE VARCHAR(1000)而 PATINDEX('%,%', @INSTR ) <>0开始SELECT @SP = PATINDEX('%,%',@INSTR)SELECT @VALUE = LEFT(@INSTR, @SP - 1)SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')插入 @TempTab(id) 值 (@VALUE)结尾返回结尾

<小时>

声明@listOfIDs varchar(1000);SET @listOfIDs = '5, 6, 7, 8, 9, 15, 28, 31, 49, 51, 59, 61';select id from [dbo].[CSVToTable] (@listOfIDs) -- 这个代码没问题5

结果正确:

678915283149515961

这会引发错误:

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

结果:

<块引用>

过程或函数 dbo.CSVToTable 指定的参数过多.

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

解决方案

简单

EXECUTE ('select id from [dbo].[CSVToTable] ('''+@listOfIDs+''')')声明@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;

• 为什么会出现这个错误?<块引用>

  过程或函数 dbo.CSVToTable 指定的参数过多.

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

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 注入.参见 和你不指定版本，我建议你使用 SPLIT_STRING() 函数 (2016+)2016+ 版本，比创建自己的不使用 WHILE 循环以获得更好的性能，因为 WHILE 循环会执行缓慢，因此您应该避免它.

示例:

• 如何将逗号分隔的值拆分为列

• 理货哦！一个改进的 SQL 8K CSV Splitter" 函数

• 从T-SQL 中的窗口函数

I have a function that converts a sting list of numbers into a table of integers:

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

The result is correct:

6
7
8
9
15
28
31
49
51
59
61

This throws an error:

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

result :

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

I need the second query because my query is dynamic. Thanks

解决方案

Simply

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

Or, which is the better way

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;

• Why I get this error?

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

Because you really pass too much parameters, more then needed, to understand this run this query and see what you are really pass to your function

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

which will return (and this is what you really trying to execute)

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

instead of (which is what you need)

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

---

• Ok, but why sp_executesql is better than exec?

Simply, EXEC will forces you to concatenate all of your variables into one single string, that's the worst thing about it, and that makes your code fully open to SQL injection. See Bad Habits to Kick : Using EXEC() instead of sp_executesql, this doesn't mean that sp_executesql is 100% secure, but it allows for statements to be parameterized while EXEC() dosn't, therefore It’s more secure than EXEC in terms of SQL injection.

---

Finally, since you tag and you don't specify the version, I suggest that you use SPLIT_STRING() function (2016+) rathar than yours, and if you don't have 2016+ version, than create your own without using WHILE loop to gain more good performance, cause WHILE loop will perform slow, thus you should avoid it.

Examples:

• 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

这篇关于如何将包含列表的变量传递给动态 SQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！