checking whether a stored procedure returns anything

Question

Post reply

checking whether a stored procedure returns anything

Viewing 15 posts - 16 through 30 (of 32 total)

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

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

I don't use it in this case. It gets used somewhere else.

I was implying that the sp that needs the count of rows doesn't use the data.

Either way, this is beside the point.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

So, you are adding extra call for SP just to count rows in recordset?

Your company spent too much money on server and now you need to make it occupied?

Don't you think the number of rows could be different in those calls?

What stops you from counting rows where you retrieve the actual recordset?

This way you make sure COUNT will ferlact the actual number of rows in returned recordset.

Why you cannot add OUTPUT parameter to SP to return @@ROWCOUNT?

_____________
Code for TallyGenerator

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

I can see where you are coming from, but it's a little difficult to explain all the WHY's and HOW's of what I am trying to accomplish.

All I want to know is how I can write a SP to ONLY return the number of rows returned by another SP.

I have tried:

exec sp_getAllEmployees

select @@ROWCOUNT

But this returns both the results of the sp_GetAllEmployees sp, as well as the rowcount. I ONLY want the rowcount.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 4

does a 'little difficult' mean you dont understand it yourself? This doesnt sound like a real problem. Use @@rowcount on the first sp and pass to the second as input which is simply returned. What you r doing sounds very wrong you shouldnt need the second sp call.

www.sql-library.com[/url]

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

Perhaps you are correct... but:

At one point my app just needs to know whether there are any employees. It doesn't need to know all the data. Wouldn't it be wrong to pass all the data back to the app if it doesn't need it at this time? Passing lots of unnecessary data is bad practice, is it not?

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 6

create proc usp_anyemployees

declare @wellisthere int

if exists (select 1 from employees)

set @wellisthere =1

else

set @wellisthere =0

select @wellisthere

really you need to put an output param into your 1st sp and return that if @@rowcount >0

www.sql-library.com[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 7

So what?

What you gonna do if there are some employees?

Pass the resultset to application?

And if there are no employees you won't pass anything but several bytes overhead for empty recordset.

_____________
Code for TallyGenerator

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

No, these 2 things are used for different things.

I check to see if there are any employees. If there are but pemployee processing is off, I do something like send an email. If there are no employees and employee processing is off, it's ok, nothing needs to be done.

If employee processing is on, I get all the employee data from the 1st sp (sp_GetAllEmployees).

Employee processing can be set to on or off in the config file.

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

And I should mention that sp_getAllEmployees is not simply a select * from a single employee table.

If it was, then I wouldnt have a problem just doing a select count(*) from that table for the 2nd SP.

sp_getAllEmployees contains lots of joins and logic, which is why I don't want to repeat it in the 2nd SP just to get the count.

Sergiy SSC Guru Points: 110208 More actions · Answer 10

So, you SP is just single SELECT?

That's why you came up with idea of COUNT(*) from SP.

To encapsulate complex joins you must use VIEWs, not SPs.

Then your SP will become :

SELECT {column list}

FROM dbo.AllEmployees -- name of the view

WHERE ... -- if you need to filter something out.

For counting you may use another SP having

SELECT COUNT(*)

FROM dbo.AllEmployees

or use same SP but include parameter in it:

create proc dbo.GetAllEmployees @Rowcount int OUTPUT

AS

IF @Rowcount = 1 -- count only

BEGIN

SELECT @Rowcount = COUNT(*)

FROM dbo.AllEmployees

RETURN

END

SELECT .....

FROM dbo.AllEmployees

SELECT @Rowcount = @@Rowcount

GO

If you call SP with @Rowcount = 1 you've got nothing back, and @Rowcount will contain number of rows in view AllEmployees.

Otherwise you'll get back full resultset, parameter @Rowcount will contain number of rows returned.

But be prepared to be blamed when you "count" run will return not the same number as actual run. Unless you can disconnect all users from that remote database.

_____________
Code for TallyGenerator

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

Thanks for the info. You are absolutely correct.

However, I was using a SP because I need to pass an argument which will be used in the WHERE clause. VIEWS don't take arguments.

Plus sp_GetAllEmployees goes off an gets some data from remote databases through linked servers...

I can't really create a VIEW to get everything and then get the sp to filter it because the VIEW would be very inefficient.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Who told you such cr.p about views?

If you do it right view is more efficient than query in SP.

_____________
Code for TallyGenerator

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

I'm just ignorant in that case.

How would I go about it?

BTW can views even get data from linked servers?

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

Okay, you're 100% correct about views. Forgive my ignorance.

Just tested it out (without the linked servers part) and it's working great.

If VIEWs support linked servers, then I'll add that and it will be done.

If not, I can always add that to the sp which is probably more logical anyway.

Sergiy SSC Guru Points: 110208 More actions · Answer 15

You must create both view and SP using it ON REMOTE SERVER.

And then just call it from here.

_____________
Code for TallyGenerator