How to block returning a resultset from stored procedure?

I have a stored procedure that returns multiple resultsets, it looks some开发者_JS百科thing 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?

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

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
)

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

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

;
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)