如何阻止从存储过程返回结果集?

您可以在这里选择几个选项,您必须在您的环境中进行测试,以查看哪个选项效果最好。

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

第二种选择是使用临时表/变量:

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

与其他好答案略有不同的方法是,您可以使用联合:

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

保持简单:

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)