Return Recordsets from Dynamic Queries called by EXEC

By 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)

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.


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

Temu Canada Black Friday Sale: 90% Off {acs540599} with Free Shipping 2024 (4h)

Temu Black Friday Deal: $200 Off {aci622722} with Free Shipping 2024 (4h)

Temu Black Friday Special: $200 Off {acx377627} with Free Shipping 2024 (4h)

New Temu Pakistan Coupon Code: {act530281} Rs. 15000 with Free Shipping 2024 (5h)

Special Temu Pakistan Coupon Code: {acs540599} 90% Off with Free Shipping (5h)

Error query return more than one value (6h)

Verbose truncation warnings setting not working (13h)

SQL Availability groups (15h)

- Advertisement -