Return Recordsets from Dynamic Queries called by EXECBy Chris Miller on 23 August 2000 | 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) 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), 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.
|
- Advertisement - |