CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
RaJesh RiJo
RaJesh RiJo

Reputation: 4412

SQL Server - Assign value to variable inside Dynamic SQL

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.

Upvotes: 3

Views: 7490

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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

Upvotes: 1

gotqn
gotqn

Reputation: 43626

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.

Upvotes: 4

Related Questions