如果存在，当条件SQL的情况之间声明(if exists statement between a c

在我的存储过程中间，我有下面的代码片段：

case
  when l.codeleasestatuscode = '5' and priorleaseid is null 
  and l.leaid in(select col1 from Waitlisthousehold)
then '2'
else l.codeleasestatuscode
end

然而，最后的条件，其中i具有从表中选择Waitlisthousehold，有一些问题。 并非所有的数据库有一个表。 所以我希望在表中存在被列入去年的状态。 但我发现了错误，当我尝试这样做：

case
when l.codeleasestatuscode = '5' and priorleaseid is null
IF EXISTS(select * from information_schema.tables where table_name='WaitlistHousehold')
  begin
    and l.leaid in(select col1 from Waitlisthousehold)
  end
then '2'
else l.codeleasestatuscode
end

让我怎么办呢正确？

这个片段是内部的从声明（从表1上a.id = b.id和情况下，当一个连接表2 b ..）

Answer 1:

你可以有另一种情况，当你的一个内部条款或检查表不存在

CASE
WHEN l.codeleasestatuscode = '5' and priorleaseid is null
    CASE WHEN EXISTS(SELECT * FROM information_schema.tables WHERE table_name='WaitlistHousehold')
        THEN
            CASE WHEN l.leaid in(select col1 from Waitlisthousehold) THEN '2' 
            ELSE l.codeleasestatuscode END
        ELSE '2' END          
    THEN '2'END
ELSE l.codeleasestatuscode
END

Answer 2:

你无法将使得那样的select语句中添加。 你可以这样做：

IF EXISTS(select * from information_schema.tables where table_name='WaitlistHousehold')
  BEGIN
  SELECT whatever,
  case
  when l.codeleasestatuscode = '5' and priorleaseid is null
    and l.leaid in(select col1 from Waitlisthousehold)
  then '2'
  else l.codeleasestatuscode
  end AS whatever1
  FROM wherever
  END
ELSE
  BEGIN
  SELECT whatever,
  case
  when l.codeleasestatuscode = '5' and priorleaseid is null
  then '2'
  else l.codeleasestatuscode
  end AS whatever1
  FROM wherever
  END

文章来源: if exists statement between a case when condition sql