Return Recordsets from Dynamic Queries called by EXEC
By Chris Miller
on 23 August 2000
| 0 Comments
| Tags: SELECT
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.
