1

How to get output from exec (using strings) and assign it to a local variable?

Code:

declare @qry nvarchar(500)
declare @StateId nvarchar(10) 

set @qry= 'Select top 1 StateId FROM '+@TableName+' where '+@ColumnName+'='+str(@BusinessId)
exec(@qry)

I want 

@StateID = exec(@qry)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sunny Singh
  • 127
  • 1
  • 12
  • Possible duplicate of [How to set value to variable using 'execute' in t-sql?](http://stackoverflow.com/questions/7229070/how-to-set-value-to-variable-using-execute-in-t-sql) – Giorgos Altanis Mar 23 '17 at 06:23

2 Answers2

3
declare @qry nvarchar(max) = 'select @id = id from ts_trails where id = 500'
declare @id nvarchaR(500)

exec sp_executesql @qry, N'@id int out', @id out

select @id

USE sp_executesql then make your variable the output parameter

beejm
  • 2,381
  • 1
  • 10
  • 19
0

If you want only one record:

 Declare @id int;
 select @id= column_id from yourtable
 select @id

This is passing data to variable

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30