SQL Server : select from exec using strings

Question

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)

Possible duplicate of How to set value to variable using 'execute' in t-sql? — Giorgos Altanis
– Giorgos Altanis, Commented Mar 23, 2017 at 6:23

beejm · Accepted Answer · 2017-03-23 06:08:32Z

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

answered Mar 23, 2017 at 6:08

beejm

2,4811 gold badge13 silver badges19 bronze badges

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

Comments

Vijunav Vastivch · Accepted Answer · 2017-03-23 06:16:51Z

0

If you want only one record:

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

This is passing data to variable

answered Mar 23, 2017 at 6:16

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

1 Comment

Vijunav Vastivch Over a year ago

Then you can change from int to varchar

Collectives™ on Stack Overflow

SQL Server : select from exec using strings

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related