SQL Server - Assign value to variable inside Dynamic SQL

Question

I wanted to assign value to out variable inside a dynamic query.

SET @query = 'IF EXISTS(Condition)
BEGIN
  --Some action goes here
  SET @result= 1
END
ELSE
BEGIN
  SET @result= 2
END'
EXEC(@query)

When am trying to execute this query, I am getting an error:

Must declare the scalar variable "@result".

How can I set value to variable inside dynamic query?

Thanks in advance.

Answer 1

Just try this:

DECLARE @result INT
       ,@query NVARCHAR(MAX);

SET @query = 'IF (1 = 0)
BEGIN
  --Some action goes here
  SET @result= 1
END
ELSE
BEGIN
  SET @result= 2
END';

EXEC sp_executesql @query, N'@result INT OUTPUT',@result =  @result OUTPUT

SELECT @result;

You can use sp_executesql in order to execute dynamic T-SQL statement and initialize SQL variables. In the sp_executesql you need to pass the parameter definition and then parameter mappings.

Answer 2

When you Execute a Query String, It is Considered as a separate session, so The variables that you have declared in the current windows won't be accessible whereas you can access the Temporary tables.

So you have to declare the variable inside the string. Your Query can be re-written as below

SET @query = '
DECLARE @result INT
IF EXISTS(Condition)
BEGIN
  --Some action goes here
  SET @result= 1
END
ELSE
BEGIN
  SET @result= 2
END'
EXEC(@query)

Or Store the result in a table variable and access it from there

DECLARE @Tablevar TABLE
(
 Result INT
)

SET @query = '
    IF EXISTS(Condition)
    BEGIN
      --Some action goes here
      select 1
    END
    ELSE
    BEGIN
      SELECT 2
    END'

INSERT INTO @Tablevar 
    EXEC(@query)

select @Result = Result FROM @Tablevar