代码之家 › 专栏 › 技术社区 › Adam Tegen

具有条件结果的存储过程

stored-procedures sql-server-2008 sql-server sql

Adam Tegen · 技术社区 · 14 年前

SELECT * from T where T.A = @a and T.B = @b

如果返回行,则返回那些行,否则返回

SELECT * from T where T.A = @a and T.B IS NULL

编辑:

它认为应该有一种方法来创建一个过程,以便它运行第一个查询一旦并运行第二个查询 仅在必要时 .

结束编辑。

我所能管理的最好的方法是如下(理论上)运行第一个查询两次,除非它是缓存的:

IF EXISTS (SELECT * from T where T.A = @a and T.B = @b) THEN
    SELECT * from T where T.A = @a and T.B = @b
ELSE
    SELECT * from T where T.A = @a and T.B IS NULL

值得一提的是,这是在MicrosoftSQLServer2008中实现的

7 回复 | 直到 13 年前

Martin Smith 14 年前

SELECT * from T where T.A = @a and T.B = @b


IF (@@ROWCOUNT = 0)
BEGIN
    SELECT * from T where T.A = @a and T.B IS NULL
END

Blorgbeard 14 年前

declare @result1 table ( ... )
insert into @result1 select * from T where T.A = @a and T.B = @b

if (@@rowcount = 0)
   select * from T where T.A = @a and T.B is null
else
   select * from @result1

Thomas 14 年前

Select ...
From T
Where T.A = @a
    And T.B = @b
Union All
Select ...
From T
Where T.A = @a
    And T.B Is Null
    And Not Exists  (
                    Select 1
                    From T
                    Where T.A = @a
                        And T.B = @b
                    )

Select ...
From T
Where T.A = @a
    And T.B = @b
Union All
(Select ...
From T
Where T.A = @a
    And T.B Is Null
Except
Select ...
From T
Where T.A = @a
    And T.B = @b)

a1ex07 14 年前

也可以在一个查询中完成:

SELECT * from T where (T.A = @a and T.B = @b) OR
( 0=(SELECT COUNT(*) T1 where (T1.A = @a and T1.B = @b) ) 
  AND T.A = @a and T.b IS NULL)

Tomek Szpakowicz 14 年前

我不知道在性能方面是否有帮助,但您可以尝试表值函数:

create function fun(@a <aType>, @b <bType>)
 returns @result (<...columns...>)
as begin
 insert into @result
 select * from T where T.A = @a and T.B = @b;

 if  (@@ROWCOUNT = 0) begin
  insert into @result
  select * from T where T.A = @a and T.B is null;
 end;
 return;
end;
GO

但我怀疑这是否有用。

如果这里真的有性能问题,我会回头看看这个数据库设计。为什么有空值?你为什么要试两个过滤器?它能以不同的方式建模吗?如果不是,可能是一点反规范化?

brasofilo chenguang 6 年前

尝试这样做,如果第一个select返回行,那么它将返回,如果第一个select失败,那么下一个select将返回,或者最后一个select:

IF EXISTS(SELECT * FROM Customers 

     INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID where Customers.CustomerID='BERJGS')
    BEGIN
             SELECT * FROM Customers 
                    INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID where Customers.CustomerID='BERJGS'
              PRINT 'TOLA'
              RETURN
     END
ELSE
     BEGIN
               SELECT * FROM Customers 
                    INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID where Customers.CustomerID='6CHOPS'             
              IF @@ROWCOUNT > 0             
                    RETURN              
              --RETURN
     END



SELECT * FROM Customers  where Customers.CustomerID='FRANK'

-2

eKek0 14 年前

编辑问题被编辑后,答案被编辑了。

CREATE PROCEDURE myconditionalsp
@a  <type>,
@b  <type>
AS

SELECT * from T 
where 
    -- the second condition is true AND the first condition is false
    (((T.A = @a) and (T.B IS NULL)) AND NOT ((T.A = @a) and (T.B = @b)))
    OR 
    -- the first condition is true (regardless what is the second condition)
    ((T.A = @a) and (T.B = @b))
GO