SQL Server - Test the result of a stored procedure

Question

In SQL Server, it is possible to test the result of a stored procedure to know if the result return rows or nothing ?

Example :

EXEC _sp_MySp 1, 2, 3

IF @@ROWCOUNT = 0
BEGIN
    PRINT('Empty')
END
ELSE
BEGIN
    PRINT(@@ROWCOUNT)
END

But @@ROWCOUNT always return 0 so maybe is there another way of doing this ?

Answer 1

You'd have to insert the results into a table or table variable:

DECLARE @t TABLE (
  Col1 varchar(20),
  Col2 varchar(20)
)


INSERT INTO @t
EXEC _sp_MySp 1, 2, 3

IF EXISTS (SELECT 1 FROM @t) PRINT 'Empty'

If you can change the stored proc, then you could have it return @@ROWCOUNT as it's return code or as an output param:

CREATE PROC _sp_MySp
  @p1 int,
  @p2 int,
  @p3 int
AS
  SELECT * FROM Table WHERE Col1 = @p1
  RETURN @@ROWCOUNT

DECLARE @rc int
EXEC @rc = _sp_MySP 1, 2, 3
IF @rc = 0 PRINT 'Empty'
ELSE PRINT CONVERT(varchar, @rc)

Answer 2

If I'm not mistaken EXECUTE statements preserve the previous @@ROWCOUNT. Therefore your statement will always return 0. Try replacing "EXEC _sp_MySp 1, 2, 3" with the actual statements from your SP.

CREATE PROCEDURE _sp_MySp
   @p1 int, @p2 int, @p3 int
AS
   SELECT ... FROM ... WHERE ...

IF @@ROWCOUNT = 0
   BEGIN
       ...
   END
ELSE
   BEGIN
       ...
   END