Styjun

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

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)
  

  
  

• 
  

  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)
  

  
  

• 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:

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