saving the count from a dynamic sql statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

saving the count from a dynamic sql statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-07 : 07:41:57

Bob writes "I need to dynamically build a sql statement to first find the number of rows I would expect in my result set (same dynamic query, but result set would be actual data). How do I go about saving that count in a variable so I can process against it?

Thanks very much in advance"

dsdeming

479 Posts

Posted - 2003-08-07 : 07:51:01

If you have to know the number of rows before you actually retrieve them, you'll need to do a count:

DECLARE @i int

SELECT @i = COUNT(*)
FROM TableName
WHERE Condition = Whatever

SELECT *
FROM TableName
WHERE Condition = Whatever

Dennis

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 10:01:21

I guess the operative word here (ugh) is DYNAMIC...



USE Northwind
GO

CREATE TABLE bk_Temp (RC Int)

DECLARE @SQL varchar(8000), @rc int

SELECT @SQL = 'INSERT INTO bk_Temp(RC) SELECT COUNT(*) FROM Orders'

EXEC (@SQL)

SELECT @rc=RC FROM bk_Temp

SELECT @rc

DROP TABLE bk_temp

Brett

8-)

SELECT POST=NewId()

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-07 : 10:19:50

I would pass a parameter out of your dynamic SQL using sp_executeSQL ... the bk_Temp could lead to some concurrency problems.

Jay White
{0}

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-07 : 12:32:24

I dont see why you need dynamic SQL here, Brett. What Dennis has posted seems to do the job adequately without any dynamic SQL. And I think Jay has a point there, you'd be better off using a temp table instead of a (permanent?) table, think what happens if somebody else is running the same proc at the same time with a different set of conditions, and consequently a different number of records.

Owais

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 12:40:53

He said he wanted to use dynamic SQL, and that's the only ting I could come up with...the temp table didn't work

I didn't think to try sp_executeSQL yet though...

Brett

8-)

SELECT POST=NewId()

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-07 : 12:47:42

quote:
Originally posted by X002548

He said he wanted to use dynamic SQL, and that's the only ting I could come up with...the temp table didn't work

I didn't think to try sp_executeSQL yet though...

.... now I uderstand why you cut yourself off my earlier post.

----------------
Shadow to Light

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources