2

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
  • 2
    Your @variable1 is null in this script; so, this : SET '+CAST(@variable1 AS VARCHAR)+... will be null ; ps: CAST(AS VARCHAR) should be always CAST(AS VARCHAR(XX)) – Sabin Bio Mar 21 at 13:38
  • ok but is there a way to go over that ? – Matthieu RGX Mar 21 at 13:39
  • you should provide more details; you could initialize it with a value : DECLARE @variable1 INT =0 – Sabin Bio Mar 21 at 13:40
  • you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details ! – Matthieu RGX Mar 21 at 13:43
  • 1
    i've edit my question ! – Matthieu RGX Mar 21 at 13:53
5

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
  • thanks a lot for your answer ! i will check it out and come back to you ! – Matthieu RGX Mar 21 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.