How can you tell if a Sql Server Stored Procedure returns records

Question

Within tsql I'm calling a tsql stored procedure that returns a record set. I want to be able to tell if the record set is empty or not empty.

For some reason @@rowcount always returns 1.

What is the best way to do this?

One more thing I'm not in a position to edit this stored procedure.

Answer 1

Use @@rowcount in the inner stored proc, pass back out as an output paramter. Use @@rowcount immediately after the SELECT in the inner stored proc. And call like this:

EXEC dbo.InnerProc @p1, ..., @rtncount OUTPUT

or...

Use RETURN @@rowcount in the inner stored proc immediately after the SELECT. And call like this:

EXEC @rtncount = dbo.InnerProc @p1, ...

Edit:

If you can't edit the proc, you have to load a temp table and manipulate that.

CREATE TABLE #foo (bar int...)

INSERT #foo
EXEC MyUntouchableProc @p1
SELECT @@ROWCOUNT

@@ROWCOUNT fails because it only shows the last statement count, not the last SELECT. It could be RETURN, END (in some cases), SET etc

Answer 2

Use @@ROWCOUNT

From msdn:

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Here's an example:

USE AdventureWorks2008R2;
GO
UPDATE HumanResources.Employee 
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
ELSE
PRINT @@ROWCOUNT + ' records updated';
GO

If you are using .net and making use of a SqlDataReader you can make use of .HasRows method or the .count of those records. The other thing you could do is pass an output parameter to your sproc, store the value in the out parameter within your sproc. Then when you return to .net you will have this value (the number of records affected by the sproc).

From MSDN:

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

Also make sure you SET NOCOUNT ON