CODEWITHSUNDEEP
sqlt-sqldynamicquery
Moran Barzilay
Moran Barzilay

Reputation: 153

How to extract a value from Dynamic SQL result?

I'm trying to get a few values from a dynamic SELECT

This is my 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 + ' FROM ' + @tableName + ' WHERE mytime>=''' + @varDate + ''' ORDER BY mytime'
PRINT @sqlCommand
EXEC(@sqlCommand)

When I print the SQL command, this is what I get:

SELECT top 1 @myTime=mytime, @myNum=col1 
FROM table 
WHERE mytime>='Jul 25 2017  4:40PM' 
ORDER BY mytime

When I try to EXEC it, I get this error:

Must declare the scalar variable "@myTime".

If I do this:

SET @sqlCommand = 'SELECT top 1 mytime, ' + @colName + ' FROM ' + @tableName + ' WHERE mytime>=''' + @varDate + ''' ORDER BY mytime'

It works well, but I need to use that data.

Thanks in advance.

Upvotes: 0

Views: 731

Answers (3)

Christian4145
Christian4145

Reputation: 543

You should use "insert exec" to get your variable out off the dynamic sql. Or use a "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 + ' FROM ' +    @tableName + ' WHERE mytime>=''' + @varDate + ''' ORDER BY mytime'
PRINT @sqlCommand
create table #t1 (mytime datetime, col1 varchar(20))
insert #t1 (mytime, col1) EXEC(@sqlCommand)
select @mytime=mytime, @col1=col1 from #t1

I hope you got the idea.

Upvotes: 0

GoldBishop
GoldBishop

Reputation: 2861

Simple...use the Variable passing features, make to identify the Output variables last in the list of variables

Rough signature but should get you started @o_sdate, @o_edate, and @o_resp are variables declared outside of the dynamic sql

exec sp_executesql @sql
    , N'@sdate date, @edate date, @resp smallint OUTPUT'
    , @sdate = @o_sdate, @edate = @o_edate, @resp = @o_resp OUTPUT

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use sp_executesql:

exec sp_executesql @sqlCommand,
                   N'@myNum int output, @myTime datetime2 output, @vardate datetime2',
                   @myNum = @myNum output,
                   @myTime = @myTime output,
                   @vardate = @vardate;

This is a much better way to run SQL code, because handling parameters is built-in.

Upvotes: 1

Related Questions