sql-server-2005t-sql

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.

Solution

  • 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