案例陈述

Question

我得从一张名为科的表格上得到一个指导员的批准日期。如果该表没有日期(null)，那么我必须从提供表中获得一个日期，即使该表没有我要查找的数据，也必须从Term表中得到一个日期。

如果这三个都是空的，那么我需要以与absoluteExpireDate相同的方式获得指导文件。

如果absoluteExpireDate在所有三个表中都是空的，那么我需要以类似的方式获得WaitList日期。

如何创建一个case语句来处理这个问题？

到目前为止，我拥有的是：

SELECT  @dInstructApprDate = case when a.InstructorApprovalDate is null  
        then
             select @dInstructApprDate = instructorapprovaldate 
             from SSS_OfferingAcademicPeriods   
             where SSS_OfferingRegPeriods.SSS_OfferingsID = @lSSS_OfferingsID

我不知道为什么它不喜欢我在“然后”中使用select语句

任何帮助都将不胜感激。

到目前为止，我得到的功能如下：

 CREATE FUNCTION [dbo].[SSS_GetInstructorApprovalDate](@lSSS_SectionsID INT)      
 RETURNS VARCHAR(20)    
 AS      

          BEGIN      

  DECLARE   
 @dInstructApprDate DATETIME,  
 @dAddDropDate  DATETIME,  
 @lTemp    INT,  
 @lSSS_OfferingsID INT,  
 @lSSS_TermsID  INT  

 SET @lTemp = 0  

 SELECT   
 @lTemp = 1  
 WHERE   
 EXISTS (SELECT 1 FROM SSS_SectionAcademicPeriods WITH (NOLOCK) WHERE SSS_SectionsID = @lSSS_SectionsID)  

--Fetch from section level, if present - Begin  
 IF @lTemp = 1  
BEGIN  

  SELECT  @dInstructApprDate = case when a.InstructorApprovalDate is null  
  then   
   (select instructorapprovaldate from SSS_OfferingAcademicPeriods where SSS_OfferingRegPeriods.SSS_OfferingsID = @lSSS_OfferingsID)

  else   
 InstructorApprovalDate  
end   
  FROM  
   SSS_SectionAcademicPeriods a WITH (NOLOCK)  
  where   
   SSS_SectionsID = @lSSS_SectionsID

Answer 1

如果您正在检查null，则可以使用Coalesce

select coalesce
(
      (select 1 a where 1=2) --returns null
    , (select 2 a where 2=3) --returns null
    , (select 3 a where 4=4) --returns result
    , 100 --default
) x

对我来说，这将比一个案例陈述更清晰/更容易阅读，而且我怀疑它也会执行得很好。

根据您问题中包含的代码和描述，对于您来说，如下所示：

CREATE FUNCTION [dbo].[SSS_GetInstructorApprovalDate](@lSSS_SectionsID INT)      
RETURNS VARCHAR(20)    
AS      
BEGIN      

    DECLARE @dInstructApprDate  DATETIME 
    ,       @dAddDropDate       DATETIME
    ,       @lSSS_OfferingsID   INT
    ,       @lSSS_TermsID       INT  
    --,     @lTemp              INT = 0

    --I suspect you don't want this bit; but uncomment if it's required (i.e. if you only want a value when there's a matching record in the secion table, but the record's approval date's null
    --SELECT top 1 @lTemp = 1 
    --FROM SSS_SectionAcademicPeriods WITH (NOLOCK) 
    --WHERE SSS_SectionsID = @lSSS_SectionsID

    --Fetch from section level, if present - Begin  
    --IF @lTemp = 1  
    --BEGIN  

        SELECT @dInstructApprDate = coalesce
        (
            (
                SELECT InstructorApprovalDate       
                FROM SSS_SectionAcademicPeriods with(nolock)
                where SSS_SectionsID = @lSSS_SectionsID
            )
            ,
            (
                select InstructorApprovalDate 
                from SSS_OfferingAcademicPeriods 
                where SSS_OfferingsID = @lSSS_OfferingsID
            )
            ,
            (
                select InstructorApprovalDate 
                from SSS_TermsAcademicPeriods 
                where SSS_OfferingsID = @lSSS_TermsID
            )

            ,
            (
                SELECT AbsoluteExpireDate       
                FROM SSS_SectionAcademicPeriods with(nolock)
                where SSS_SectionsID = @lSSS_SectionsID
            )
            ,
            (
                select AbsoluteExpireDate        
                from SSS_OfferingAcademicPeriods 
                where SSS_OfferingsID = @lSSS_OfferingsID
            )
            ,
            (
                select AbsoluteExpireDate        
                from SSS_TermsAcademicPeriods 
                where SSS_OfferingsID = @lSSS_TermsID
            )
            ,
            (
                SELECT WaitListDate     
                FROM SSS_SectionAcademicPeriods with(nolock)
                where SSS_SectionsID = @lSSS_SectionsID
            )
            ,
            (
                select WaitListDate      
                from SSS_OfferingAcademicPeriods 
                where SSS_OfferingsID = @lSSS_OfferingsID
            )
            ,
            (
                select WaitListDate      
                from SSS_TermsAcademicPeriods 
                where SSS_OfferingsID = @lSSS_TermsID
            )

        )
    --END
    return cast(@dInstructApprDate as varchar(20)) --probably 

END

注意:取决于每次查询所需的时间，您可能希望对其进行稍微不同的处理。这里有一个替代方案/让我知道它是如何适合的：

CREATE FUNCTION [dbo].[SSS_GetInstructorApprovalDate](@lSSS_SectionsID INT)      
RETURNS VARCHAR(20)    
AS      
BEGIN      

    DECLARE @dInstructApprDate  DATETIME 
    ,       @dInstructApprDate2 DATETIME 
    ,       @dInstructApprDate3 DATETIME 
    ,       @dAddDropDate       DATETIME
    ,       @lSSS_OfferingsID   INT
    ,       @lSSS_TermsID       INT  
    --,     @lTemp              INT = 0

    --I suspect you don't want this bit; but uncomment if it's required (i.e. if you only want a value when there's a matching record in the secion table, but the record's approval date's null
    --SELECT top 1 @lTemp = 1 
    --FROM SSS_SectionAcademicPeriods WITH (NOLOCK) 
    --WHERE SSS_SectionsID = @lSSS_SectionsID

    --Fetch from section level, if present - Begin  
    --IF @lTemp = 1  
    --BEGIN  


        SELECT  @dInstructApprDate  = InstructorApprovalDate        
        ,       @dInstructApprDate2 = AbsoluteExpireDate
        ,       @dInstructApprDate3 = WaitListDate
        FROM SSS_SectionAcademicPeriods with(nolock)
        where SSS_SectionsID = @lSSS_SectionsID

        if @dInstructApprDate is null 
            select  @dInstructApprDate  = InstructorApprovalDate        
            ,       @dInstructApprDate2 = isnull(@dInstructApprDate2, AbsoluteExpireDate)
            ,       @dInstructApprDate3 = isnull(@dInstructApprDate3, WaitListDate)
            from SSS_OfferingAcademicPeriods 
            where SSS_OfferingsID = @lSSS_OfferingsID

        if @dInstructApprDate is null 
            select  @dInstructApprDate  = InstructorApprovalDate    
            ,       @dInstructApprDate2 = isnull(@dInstructApprDate2, AbsoluteExpireDate)
            ,       @dInstructApprDate3 = isnull(@dInstructApprDate3, WaitListDate
            from SSS_TermsAcademicPeriods 
            where SSS_OfferingsID = @lSSS_TermsID


        set @dInstructApprDate = coalesce(@dInstructApprDate, @dInstructApprDate2, @dInstructApprDate3)

    --END
    return cast(@dInstructApprDate as varchar(20)) --probably 

END

Answer 2

如果没有完整的查询，很难说，a代表什么？看起来您的case是更大查询的一部分，但是

SELECT
    @dInstructApprDate = 
         case
             when a.InstructorApprovalDate is null  
             then 
             (
                 select o.InstructorApprovalDate 
                 from SSS_OfferingAcademicPeriods as o
                 where o.SSS_OfferingsID = @lSSS_OfferingsID
             )
-- ...
-- you have from clause here?
-- ...

我认为您的查询可以大大简化，但直到我看到整个查询时才能说出

更新

select @dInstructApprDate = InstructorApprovalDate
from SSS_SectionAcademicPeriods
where SSS_SectionsID = @lSSS_SectionsID

if @dInstructApprDate is null
    select @dInstructApprDate = instructorapprovaldate
    from SSS_OfferingAcademicPeriods
    where SSS_OfferingsID = @lSSS_OfferingsID

Answer 3

每个选择只需要一个变量赋值。

SELECT  @dInstructApprDate = case when a.InstructorApprovalDate is null  
        then  (select  instructorapprovaldate from SSS_OfferingAcademicPeriods)
....

但是更好的方法是将它分割成单独的选择--更容易阅读，更容易维护，更好地让服务器执行。

SELECT  @dInstructApprDate = InstructorApprovalDate FROM section

IF @dInstructApprDate IS NULL -- if it's null after first assignmnent
     SELECT  @dInstructApprDate = InstructorApprovalDate FROM SSS_OfferingAcademicPeriods

IF @dInstructApprDate IS NULL -- if it's still null
     SELECT  @dInstructApprDate = TermDate FROM Term