Return to Return Recordsets from Dynamic Queries called by EXEC

Return Recordsets from Dynamic Queries called by EXEC

Written by Chris Miller on 23 August 2000

Yong writes "I have a question regarding getting recordset from dynamic queries called using T-SQL EXEC in SQL 7.0.

Yong writes "Dear Gurus,

I have a question regarding getting recordset from dynamic queries called using T-SQL EXEC in SQL 7.0.

EXAMPLE:

DECLARE @resultcount int
DECLARE @Tablename varchar(255)
DECLARE @condfield varchar(1000)
DECLARE @result varchar(1000)
DECLARE @sqlquery varchar(1000)

@sqlquery="SELECT COUNT(*) from "
@sqlquery=@sqlquery+@Tablename
@sqlquery=@sqlquery+" WHERE "+@condfield
@sqlquery=@sqlquery+"="
@sqlquery=@sqlquery+@result
EXEC(@sqlquery)

How do I get an answer from this EXEC(@sqlquery)? Thanks. Yong"

One way to do this is to insert the data into a temp table:

create table #foo (RecordCount int)

insert into #foo
exec (@SQLQuery)

If you just need the rowcount, you can select all the rows and snag the value out of @@ROWCOUNT. You could always use a permanent table instead of a temp table for more persistent results.

Just a quick word of advice for debugging, when we do this in our environment, we usually do something like this:

declare @CRLF char(2),
@SQL char(1000)
set @CRLF = char(10) + char(13)

set @SQL = "select col1, col2, col3, col4" + @CRLF
set @SQL = @SQL + "from mytable" + @CRLF


The @CRLF value contains a carriage return/linefeed pair, and if you need to debug your @SQL variable, you can print it and it will print on multiple lines so it's easier to read.

rocketscientist.