checking whether a stored procedure returns anything

Question

Post reply

checking whether a stored procedure returns anything

Paul_J

Hall of Fame

Points: 3698
More actions
December 27, 2006 at 7:57 pm

#153358

Hi all,
This may be a simple one... but I can't get it to work... probably because my brain has gone into holiday mode without asking me! 😉
I have a stored procedure that returns some data (like a table).
I want to write some T-SQL that calls the stored procedure and determines whether it is empty or not. Something like:
select * from (exec stored_procedure_name)
Is this possible?
I would also like to manipulate the stored proc in other ways, such as calculating how many rows it returns, like:
select count(*) from (exec stored_procedure_name)
NOTE: I do not want my SQL to know anything about the inner working of the stored procedure, so for example, I dont want to create a table, fill it with the stored proc data and then do a count on the table.
Cheers

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Where you suppose to place this counting script?

_____________
Code for TallyGenerator

Paul_J Hall of Fame Points: 3698 More actions · Answer 2

In another stored proc. 😉

I have a stored proc that returns a table, for example, something like: sp_GetAllEmployees.

Then I want to have a separate stored proc that determines if there are any exployees, e.g. sp_CountEmployees or something similar.

These are for 2 different purposes.

I would like sp_CountEmployees to somehow use sp_GetAllEmployees, so that the logic of getting the employees is not duplicated.

Paul_J Hall of Fame Points: 3698 More actions · Answer 3

As far as getting the count of rows goes, the following gets it (but it also gets the entire resultset from the sp_GetAllEmployees stored proc):

exec sp_GetAllEmployees

select @@rowcount

Sergiy SSC Guru Points: 110208 More actions · Answer 4

I still cannot figure out - what suppose to call sp_GetAllEmployees and use its results?

P.S. Don't use "sp_" prefix in names for SP. It's VERY bad practice.

_____________
Code for TallyGenerator

Paul_J Hall of Fame Points: 3698 More actions · Answer 5

sp_CountEmployees or any other stored proc will call it.

P.S. No worries... I was just using sp_ for clarity in this example, so it's clear that that is the stored proc.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

So, if sp_CountEmployees will call sp_GetAllEmployees how it's gonna use returned resultset?

_____________
Code for TallyGenerator

Paul_J Hall of Fame Points: 3698 More actions · Answer 7

I just want sp_CountEmployees to return the number of rows sp_GetAllEmployees returns. That's it.

DaveItz SSCrazy Points: 2146 More actions · Answer 8

If you don't mind building a temporary table whose structure covers the stored procedure's results you can do something like:

CREATE PROCEDURE dbo.usp_MyStoredProc
AS
SELECT 'a' union all
SELECT 'b' union all
SELECT 'c' union all
SELECT 'd'

and then

create table #results(letter char(1))

set nocount on
insert into #results
exec usp_MyStoredProc
set nocount off

select count(*) from #results

drop table #results

Sergiy SSC Guru Points: 110208 More actions · Answer 9

So, you don't use resultset from sp_GetAllEmployees anywhere. Right?

_____________
Code for TallyGenerator

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

hi

will using a function instead of a procedure help. performance might not be very good though.

"Keep Trying"

Paul_J Hall of Fame Points: 3698 More actions · Answer 11

Yep, the only way I use the resultset is to determine how many rows are returned.

Also, a function is not an option as I will be calling the stored procs through a Linked Server. And as far as I know, it is not possible to call a function remotely through a linked server...

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Why you need this SP if you don't use its result?

_____________
Code for TallyGenerator

Paul_J Hall of Fame Points: 3698 More actions · Answer 13

Sorry, I wasn't very clear. I use both SP's. I just don't use the actual "data" returned by sp_GetAllEmployees. All I am interested in is finding out how many rows sp_getAllEmployees is returning.

Sergiy SSC Guru Points: 110208 More actions · Answer 14

So, if you don't use actual data returned by sp_GetAllEmployees, why you need this SP at all?

_____________
Code for TallyGenerator