SQL Server Dynamic Query – Set Value of Variable Declared Outside

sql servert-sql

I have a stored procedure which i simplified this way :

DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET '+CAST(@variable1 AS VARCHAR)+' = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'
EXEC(@SQL)

But this script don't give me anything ! I'm pretty sure it's something related to scope. The thing is that i need to declare the variable outside the dynamic query.

thanks for help !

EDIT :

WHILE LOOP UNTIL SELECT COUNT xxx = 0
BEGIN
    DECLARE @variable1 INT

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = '
                EXEC STORE PROC WITH PARAMETER @Param1 = @variable1 (first loop @Param1 is null)

                STORE PROC RETURN A VALUE

                SET @variable1 with return value of store proc
                and use it in second loop, third loop...
    '
    EXEC(@SQL)
END

Best Answer

Three things worth mentioning:

  • Always use PRINT to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holding NULL.

    DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'

PRINT(@SQL)

-- EXEC(@SQL)

enter image description here

  • The reason because the dynamic SQL is NULL is because you are concatenating a NULL value which is the @variable1 contents. I believe you wanted to write down the text '@variable1' as literal:

    DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET @variable1 = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'

PRINT(@SQL)

enter image description here

  • Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read @variable1 since it's not declare anywhere. If we execute the dynamic SQL:

enter image description here

The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT option. This will require to use the SP sp_executesql rathen than a direct EXEC:

DECLARE @externalVariable INT

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT = 1
    SET @resultVariable = @variable2'

EXEC sp_executesql
    @stmt = @SQL,
    @params = N'@resultVariable INT OUTPUT',    -- Declare the "input" parameters for the dynamic SQL
    @resultVariable = @externalVariable OUTPUT  -- Supply the "input" parameters for the dynamic SQL

SELECT 
    Result = @externalVariable -- Read the updated value

Note that I changed data types to NVARCHAR since sp_executesql works with unicode inputs.

Another example with more parameters:

DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT

DECLARE @SQL NVARCHAR(MAX) = '
    SET @multiplicationResult = @inputFactor1 * @inputFactor2'

EXEC sp_executesql
    @stmt = @SQL,
    @params = N'
        @multiplicationResult INT OUTPUT,
        @inputFactor1 INT,
        @inputFactor2 INT',
    @multiplicationResult = @result OUTPUT,
    @inputFactor1 = @firstNumber,
    @inputFactor2 = @secondNumber

SELECT 
    Result = @result -- 45!

If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL and literal values:

DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
        StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
        FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '

PRINT(@DynamicSQL)

EXEC(@DynamicSQL)

Printed:

SELECT
    DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019  3:27PM'),
    StringVariableContents = '',
    FloatVariableContents = CONVERT(FLOAT, '15.124')

Result:

DateVariableContents        StringVariableContents  FloatVariableContents
2019-03-21 15:28:00.000                             15.124
Related Question