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.

Solution 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

Solution 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

Solution 3

Edit

You want to calculate @@rowcount immediately after the SELECT statement. If anything happens in between, the @@rowcount will be updated for each new statement. You could evalute @@rowcount inside the stored procedure and pass back as an output parameter.

Edit

Also make sure you SET NOCOUNT ON.

Or

Run a select count(*) from... against the underlying data set in the stored procedure. You could spit that out as an output parameter.

Solution 4

Select @@rowcount: After executing the stored procedure.

View more solutions

Comments

[codingguy3000]

Yeah but records are not captured in format that I can execute a select * against.

[Jody]

Fortunately @@rowcount is tied to current scope, so you can depend on it even when triggers are present. Unlike @@Identity. simple-talk.com/community/forums/thread/2130.aspx

[codingguy3000]

@@rowcount is returning 1 even if the record set is empty.

[LoganS]

@codingguy3000 see my notes above.

[codingguy3000]

@@rowcount should work, however I'm getting a value of one (return from @@rowcount) when the record set return by the stored procedure is empty.

[mellamokb]

Make sure you SET NOCOUNT ON.