Set in dynamic query value of variable declared outside dynamic query

Question

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

• Sabin B: 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))
• Matthieu RGX: ok but is there a way to go over that ?
• Sabin B: you should provide more details; you could initialize it with a value : DECLARE @variable1 INT =0
• Matthieu RGX: you mean initialize the value inside the dynamic query or outside ? i will try to edit my question to provide more details !
• Matthieu RGX: i've edit my question !

Answer 1

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

Answer 2

try this query

DECLARE @SQL VARCHAR(MAX)
DECLARE @a int
set @a = 1
WHILE @a < 6 
BEGIN
SET @SQL = '
 DECLARE @variable ' + cast(@a as varchar) + ' INT '
PRINT(@SQL)
 SET @a = @a + 1 
END 
PRINT(@SQL)

• Rohit Gupta: Pmease enhance your answer by explaining what the query does.