Stored Procedure with No results

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

Transact-SQL (2000)

Stored Procedure with No results

Author

Topic

EvilTadpole
Starting Member

9 Posts

Posted - 2006-02-15 : 15:02:41

I have a Stored Procedure that executes a second stored procedure and sends the results out automatically in an email attachment. My problem is that when there are no results from the second sp, the attachment get sent with a "The Specified statement did not generate any output" message where that data in rows should be.

I would like to find a way to see if there are any results from the second sp and if there are not skip the send email part. The big issue with this is that the second sp is dynamic (we have many different ones to send out), so altering the second sp to have an output variable is not a viable option. I can't seem to get a cursor to read a sp, I don't know why, it just might not be able to do it. I was also trying to think of an alternative way where it looks at the created attachment before its sent and find the above phrase. Is it possible to search an .xls document in through T-SQL. Any ideas would be helpful.

Thanks,
Erika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-15 : 15:25:55

Modify stored procedure 1 so that it checks the value of @@ROWCOUNT after the execution of the second procedure. Here is an example:


CREATE PROC sp2
AS
SELECT 1
GO

CREATE PROC sp1
AS
DECLARE @rc int
EXEC sp2
SET @rc =  @@ROWCOUNT
IF @rc <> 0
	PRINT 'Does not equal 0'
GO

EXEC sp1

DROP PROC sp1, sp2

Tara Kizer
aka tduggan

EvilTadpole
Starting Member

9 Posts

Posted - 2006-02-15 : 16:11:04

I bow down to your glory.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources