CODEWITHSUNDEEP
sql-server-2005t-sql
codingguy3000
codingguy3000

Reputation: 2835

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

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.

Upvotes: 5

Views: 15964

Answers (4)

myseLFFF
myseLFFF

Reputation: 11

Select @@rowcount: After executing the stored procedure.

Upvotes: 1

gbn
gbn

Reputation: 432311

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

Upvotes: 5

mellamokb
mellamokb

Reputation: 56769

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.

Upvotes: 1

JonH
JonH

Reputation: 33153

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

Upvotes: 2

Related Questions