Generic Way 2 Determine # Rows Returned By Arbitrary [User] SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Generic Way 2 Determine # Rows Returned By Arbitrary [User] SP?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-27 : 11:31:23

grobyc writes "Hello,

I'm using SQL Server 7.0.

I'm hoping to find a generic way to determine the # rows returned by a [user] stored procedure. Assume that I don't know apiori the number and types of columns returned, and that the SP has "set nocount on."

What I'm looking for is something like the decidedly-disallowed:

select count(*)
from exec spFoo

Thanx"

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-27 : 11:57:57

Could use something like this:
Not super fast, but it will do what you need.

If you are using ADO, you can do Recordset.recordCount


SELECT * 
INTO #Foo
FROM MyTable 
WHERE myField = @MyField

SELECT @NumRecords = Count(*) from #Foo
SELECT * FROM #Foo

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-08-27 : 12:38:16

Do you have control of the stored procedure in question? If so, how about @@ROWCOUNT.


create procedure p_test AS
set nocount on
select a,b,c from table
select @@ROWCOUNT as rows

Then, you can get it in ADO with ADODB.RecordSet.NextRecordSet. If you don't have control of the stored procedure, you can use the ADODB.RecordSet.RecordCount property, but I believe you have to use a static cursor for that rather than the higher performance forwardonly cursor.

Cheers
-b

Edited by - aiken on 08/27/2002 12:40:56

grobyc
Starting Member

2 Posts

Posted - 2002-08-27 : 15:56:14

Thanx, but it wouldn't work. As mentioned I'm trying to get the rowcount of an unknown, black-box SP. INSERT EXEC (instead of SELECT INTO) wouldn't work if the counted-SP already does an INSERT EXEC, since that statement doesn't nest (on SQL Server 7.0).

I'm also hoping for a pure-SQL solution.

--A

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-27 : 16:03:28

Ok, how are you calling said "black-box Stored proc?"
Are you using ADO?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-27 : 16:03:31

From what I can tell there is no pure SQL solution to what you want to do... I don't know of any INFORMATION_SCHEMA that can tell you the information you want... perhaps SQLDMO can tell you what you need to know ...

grobyc
Starting Member

2 Posts

Posted - 2002-08-28 : 01:45:34

I, too, am beginning to think there's no pure [T-]SQL solution.

AFAIK SQL-DMO doesn't describe resultsets of SPs (makes sense; the SP may be using dynamic SQL that changes with hour-of-day!), and INFORMATION_SCHEMA is unlikely to either, for the same reason.

What I want to do is to write SP1 that can call a given SP2 and return a rowcount from SP2 as its only output. Surprises aside I guess I'll end up writing an SP that uses the OLE* SPs and ADO.

--A

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-28 : 11:56:22

Can't you use something like

INSERT INTO #Temp
EXEC SP2

SELECT @OutputParm = COUNT(*) FROM #Temp

in your outer SP1?

Of course, the challenge is defining #Temp in a way that would work universally. I don't think you can use SELECT INTO #Temp EXEC SP2, but you might try that because that might be able to define the table on the fly.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-28 : 12:11:18

and what about procedures that return multiple result sets...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources