How to block returning a resultset from stored procedure?

Question

I have a stored procedure that returns multiple resultsets, it looks something like this

BEGIN
    SET NOCOUNT ON;

    SELECT c1, c2, c3
    FROM t1
    WHERE id = @id

    IF (@@ROWCOUNT = 0)
    BEGIN
     SELECT c1, c2, c3
     FROM t2
     WHERE id = @id
    END
END

I use this stored procedure in my front-end ASP.NET website.

If the first SELECT statement did not return any rows I get 2 resultsets (1st one is obviously empty) in my SqlDataReader. Is there a way to return only the resultset from the last SELECT statement?

Answer 1

DECLARE @Count int;

SELECT @Count = Count(*)
FROM t1
WHERE id = @id


IF(@Count = 0)
BEGIN

SELECT c1, c2, c3
FROM t2
WHERE id = @id

END
ELSE
BEGIN

SELECT c1, c2, c3
FROM t1
WHERE id = @id

END

Answer 2

Couple of options you could take here, you'd have to test in your environment to see which works best.

First option would be to wrap the first statement in an if block similar to what you've done with the second block:

BEGIN
 SET NOCOUNT ON;

 if exists
 (
  SELECT c1, c2, c3
  FROM t1
  WHERE id = @id 
 )
 begin
  SELECT c1, c2, c3
  FROM t1
  WHERE id = @id
 end
 else
 begin
  SELECT c1, c2, c3
  FROM t2
  WHERE id = @id
 END
END

Second option would be to use a temp table/variable:

BEGIN
 SET NOCOUNT ON;

 declare @t1 table (c1 int, c2 int, c3 int)

 insert @t1 (c1,c2,c3)
 SELECT c1, c2, c3
 FROM t1
 WHERE id = @id 

 IF (@@ROWCOUNT = 0)
 BEGIN
  SELECT c1, c2, c3
  FROM t2
  WHERE id = @id
 END
 ELSE
 BEGIN
  select c1,c2,c3
  from @t1
 end

END

Answer 3

In a slightly different approach from the other good answers, you can use a union:

SELECT c1, c2, c3 FROM t1 WHERE id = @id
UNION ALL
SELECT c1, c2, c3 FROM t2 WHERE id = @id
AND NOT EXISTS (
    SELECT * FROM t1 WHERE id = @id
)

Answer 4

;
WITH
rws_1 AS(
    SELECT c1, c2, c3 FROM t1 WHERE id = @id
),
rws_2 AS(
    SELECT c1, c2, c3 FROM t2 WHERE id = @id
)
SELECT * FROM rws_1
UNION ALL
SELECT * FROM rws_2 WHERE NOT EXISTS(SELECT * FROM rws_1)

Answer 5

Keep it simple:

BEGIN        

SET NOCOUNT ON;        

if exists (SELECT 1 FROM t1 WHERE id = @id)

  SELECT c1, c2, c3                
  FROM t1
  WHERE id = @id

else        

  SELECT c1, c2, c3
  FROM t2
  WHERE id = @id

END