tsql - How to extract a value from Dynamic SQL result? -

i'm trying few values dynamic select

this code:

declare @sqlcommand varchar(1000) declare @colname varchar(20) declare @tablename varchar(20) declare @mynum int declare @vardate varchar(19) declare @mytime datetime2 set @vardate = getdate() set @colname = 'col1' set @tablename = 'table' set @sqlcommand = 'select top 1 @mytime=mytime, @mynum=' + @colname + ' ' + @tablename + ' mytime>=''' + @vardate + ''' order mytime' print @sqlcommand exec(@sqlcommand) 

when print sql command, get:

select top 1 @mytime=mytime, @mynum=col1  table  mytime>='jul 25 2017  4:40pm'  order mytime 

when try exec it, error:

must declare scalar variable "@mytime".

if this:

set @sqlcommand = 'select top 1 mytime, ' + @colname + ' ' + @tablename + ' mytime>=''' + @vardate + ''' order mytime' 

it works well, need use data.

thanks in advance.

you should use "insert exec" variable out off dynamic sql. or use "double-hash"-table.

declare @sqlcommand varchar(1000) declare @colname varchar(20) declare @tablename varchar(20) declare @mynum int declare @vardate varchar(19) declare @mytime datetime2 set @vardate = getdate() set @colname = 'col1' set @tablename = 'table' set @sqlcommand = 'select top 1 mytime, ' + @colname + ' ' +    @tablename + ' mytime>=''' + @vardate + ''' order mytime' print @sqlcommand create table #t1 (mytime datetime, col1 varchar(20)) insert #t1 (mytime, col1) exec(@sqlcommand) select @mytime=mytime, @col1=col1 #t1 

i hope got idea.