How to extract a value from Dynamic SQL result?

Question

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.

Why do you need to create @sqlCommand and then execute it with exec()? — DontThinkJustGo, Jul 25 '17 at 13:50
Create a temporary table before the sqlCommand declaration and try INSERT into the temporary table instead of SELECT. Read the content of the temp table after the exec command.(EDIT: the answers with sp_executesql offer a better solution) — Cristian Rusanu, Jul 25 '17 at 13:54
Aside; If your inputs, e.g. `@colName` and `@varDate`, don't come from _absolutely trustworthy_ sources you may meet [Bobby Tables](http://bobby-tables.com/). — HABO, Jul 25 '17 at 15:29

score 1 · Answer 1 · answered Jul 25 '17 at 13:50

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.

score 1 · Answer 2 · answered Jul 25 '17 at 13:50

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

Christian4145 · Accepted Answer · 2017-07-25T21:23:29.837

0

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.

edited Jul 25 '17 at 21:23

answered Jul 25 '17 at 21:15

Christian4145

513
1
9
31

Thanks, Its just what I wanted. – Moran Barzilay Jul 26 '17 at 07:26
Hello Moran - you also could use the output parameter of sp_executesql passing the value to the calling procedure. But this won't work with older versions (before 2012?). Insert ... exec will work with every procedure. The cons: You need to know the datatype/structure of the table returned by the procedure.. – Christian4145 Jul 26 '17 at 16:51

How to extract a value from Dynamic SQL result?

3 Answers3