Query To Tell If Procedure Returns Records

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)

Query To Tell If Procedure Returns Records

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-01-15 : 14:52:41

I need a procedure that will tell me if a stored procedure returns a recordset or not. Is this possible? I can't figure this out because if the procedure returns an empty recordset I still want to know that its empty vs ones that just don't return one at all. Thanks!

- Onamuji

sica
Posting Yak Master

143 Posts

Posted - 2002-01-15 : 16:46:27

You can use OPENQUERY or OPENROWSET to execute the sp and check if it returns something like this:

SELECT COUNT(*)
FROM OPENROWSET('SQLOLEDB', '<server_name>';'<user>';'<password>',
'EXEC Pubs..byroyalty 100')

or use the classic ones

CREATE TABLE #temp (id varchar(40))
INSERt #TEMP
EXEC Pubs..byroyalty 100
SELECT COUNT(*) FROM #TEMP
DROP TABLE #TEMP

and you should get a 10

I've tested on SQL 2000 (pubs).

Sica

Edited by - sica on 01/15/2002 16:47:38

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-15 : 17:44:31

Probably even easier to use EXIST.

If (NOT) EXIST (Select ....)
BEGIN
Do something here
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources