动态SQL，其字符串包括SQL Server中的变量声明 [英] Dynamic SQL with string including variables declaration in SQL Server

问题描述

是否可以运行包含变量声明的动态SQL脚本？

Is it possible to run dynamic SQL scripts that include declaration of variables?

示例：

重要提示：此示例仅用于说明我需要实现的机制。为了简单起见，显示的计算是微不足道的。

Important note: this example is only to demonstrate the mechanism I need to implement. The shown calculation is trivial for the sake of simplicity.

我需要返回4个传递值之间的最小值，因此，以编程方式，我创建了一个包含以下代码的字符串：

I need to return the minimal value between 4 passed values so, programmatically, I create a string that contains the following code:

DECLARE @_1 INT = 12 ;
DECLARE @_2 INT = 22 ;
DECLARE @_3 INT = 32 ;
DECLARE @_4 INT = 42 ;
DECLARE @_Min   = NULL ;

SET @_Min = @_1 ;
IF (@_2 < @_Min) SET @_Min = @_2 ;
IF (@_3 < @_Min) SET @_Min = @_3 ;
IF (@_4 < @_Min) SET @_Min = @_4 ;

SELECT @_Min ;

同样，所有这些都包含在字符串变量中（例如 @_ Command ）。

Again, all this is contained in a string variable (say @_Command).

要执行此操作并获取计算结果，我将运行以下命令：

To execute this and get the result of the calculation, I would be running the following command:

EXECUTE sp_executesql @_l_Command                     , 
                      N'@_l_Result FLOAT OUTPUT'      , 
                      @_l_Result = @_l_Result OUTPUT    ;

运行它时，我收到一条错误消息，指出：

When running it, I get an error message stating:

消息156，级别15，状态1，第1行
关键字'DECLARE'附近的语法不正确。

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'DECLARE'.

很明显，我在语法上做错了事，但无法弄清楚可能是什么。

Obviously I'm doing something syntactically wrong but cannot figure out what it could be.

推荐答案

是的，您可以在动态查询中声明变量。

请在查询中设置@_Min变量类型

我运行时没有错误

Yes you can declare variables in dynamic query.
please set @_Min variale type in query
I run you query without error

DECLARE @_l_Result NVARCHAR(MAX)
DECLARE @_l_Command NVARCHAR(MAX)='
DECLARE @_1 INT = 12 ;
DECLARE @_2 INT = 22 ;
DECLARE @_3 INT = 32 ;
DECLARE @_4 INT = 42 ;
DECLARE @_Min int  = NULL ;

SET @_Min = @_1 ;
IF (@_2 < @_Min) SET @_Min = @_2 ;
IF (@_3 < @_Min) SET @_Min = @_3 ;
IF (@_4 < @_Min) SET @_Min = @_4 ;

SELECT @_Min as res ;'

EXECUTE sp_executesql @_l_Command                     , 
                      N'@_l_Result FLOAT OUTPUT'      , 
                      @_l_Result = @_l_Result OUTPUT    ;

或

EXECUTE sp_executesql @_l_Command

这篇关于动态SQL，其字符串包括SQL Server中的变量声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！