Dynamic SQL with string including variables declaration in SQL Server

Question

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

Example:

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

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 ;

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:

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.

Your best friend: PRINT @DynamicSQLVariable;. If you can't run the the SQL printed out from the PRINT statement, then neither can sp_executesql. if you're getting a Syntax error, then the reason isn't because you "can't do something" in Dynamic SQL; it's that the syntax of the Dynamic SQL is wrong. — Thom A
– Thom A ♦, Commented May 10, 2018 at 13:36
@Larnu, I was quite sure that it should be possible to declare variables (I've done this type of things using other DBMSs like MySQL and Oracle), so my first guess was that the issue is in somewhere in the syntax. — FDavidov
– FDavidov, Commented May 10, 2018 at 16:11
@FDavidov I didn't say it wasn't possible? Try this, for example: DECLARE @SQL nvarchar(MAX) = 'DECLARE @i int = 1; SELECT @i AS i;'; EXEC (@SQL); works fine. I was introducing you PRINT, and that you should be debugging the SQL that it outputs. — Thom A
– Thom A ♦, Commented May 10, 2018 at 16:14
@Larnu that's OK. The test using the PRINT output I did before posting my question of course. See Naeemaei's answer below. — FDavidov
– FDavidov, Commented May 10, 2018 at 16:17

Hamed Naeemaei · Accepted Answer · 2018-05-10 13:36:30Z

3

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    ;

or

EXECUTE sp_executesql @_l_Command

answered May 10, 2018 at 13:36

Hamed Naeemaei

10k4 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FDavidov Over a year ago

Nice!!! I'll check where my error was using your example as a basis and let you know (next week though). Thanks for now.

FDavidov Over a year ago

Well Naeemaei, not only it works but I'm returning now more than on value (outputs). Great assistance indeed!! (and, of course, you got my vote).

Amin Golmahalleh Over a year ago

You Can Use From This Command For Show Created Query In Messages Tab: ---> PRINT @_l_Command

Samuel A C · Accepted Answer · 2018-05-10 13:45:54Z

0

Apart from setting the variable type for @_Min,If you need to pass values then you should also pass the inputs as separate inputs while executing sp_executesql, like below

DECLARE @_l_Command nVarchar(max),@Params nVarchar(max)
SET @_l_Command='SET @_Min = @_1 ;
IF (@_2 < @_Min) SET @_Min = @_2 ;
IF (@_3 < @_Min) SET @_Min = @_3 ;
IF (@_4 < @_Min) SET @_Min = @_4 ;

SELECT @_Min ;'


SET @Params='@_1 INT,@_2 INT,@_3 INT,@_4 INT,@_Min INT'
EXECUTE sp_executesql @_l_Command,@Params,12,22,32,42,NULL

answered May 10, 2018 at 13:45

Samuel A C

4665 silver badges18 bronze badges

1 Comment

FDavidov Over a year ago

Since I have the actual values of the calculation inputs, it makes no sense to add them to the parameters list of sp_executesql while they could be set as assigned while building the command. This way the EXECUTE sp_executesql statement can be generalize for any type of calculations regardless of the number of inputs (which could be 1 to 100).

Collectives™ on Stack Overflow

Dynamic SQL with string including variables declaration in SQL Server

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related