Storing value in variable using dynamic sql in sql server [duplicate]

Question

Possible Duplicate:
Assign result of dynamic sql to variable

Can you help me to store a value in the given example.

DECLARE @variable VARCHAR(20)

declare @table_name varchar(20)='sample_table'

EXEC('SELECT '+@variable+'=SUM(CAST([Annual Plan & GM$] AS MONEY)) FROM '+@table_name+'')

So what is the problem you are getting with the above query? You might want to declare another var called '@SQL', and build the query in that variable. Then you can use EXEC '@SQL'. — tranceporter
– tranceporter, Commented Jan 18, 2013 at 12:32

Alex K. · Accepted Answer · 2013-01-18 12:37:01Z

3

You can return a typed output parameter using sp_executesql:

declare @variable   money
declare @table_name varchar(20)='sample_table'

declare @sql nvarchar(255) = 'SELECT @variable=SUM(CAST([Annual Plan & GM$] AS MONEY)) FROM '+ @table_name
EXEC sp_executesql @sql, 
    N'@variable money OUTPUT',
    @variable OUTPUT

select @variable

answered Jan 18, 2013 at 12:37

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1990383 Over a year ago

Thanks Alex Yah! got the result...

Collectives™ on Stack Overflow

Storing value in variable using dynamic sql in sql server [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related