IF 語句的問題

我對此儲存過程有疑問。當使用者選擇“是”時，它應該按照“是”的 if 語句中所說的去做，但它什麼也不做，也不向數據庫中插入任何東西。它適用於“否”條件。你能告訴我它不工作嗎？

ALTER PROCEDURE [dbo].[sp_ff_Insert_ProjectApproved]
(

       @project_no varchar(6)
      ,@sequence_no int
      ,@grant_programme varchar(2)
      ,@jobs_approved int
      ,@grant_amount int
      ,@Committee varchar(4)
      ,@Meeting_no int
      ,@Minute_Item int
      ,@jobs_maintained int
      ,@approval_date date
      ,@approval_by int
      ,@comments varchar(1600)
      ,@created_by int
      ,@created_date datetime
      ,@updated_by int
      ,@updated_date datetime
      ,@approval varchar(5)
      ,@ratify varchar(3)




      --,@research_type varchar(1)='T'
)

AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from


DECLARE @ReturnValue int,
       @post_approval_status varchar(3),
       @approval_status varchar(3),
       @current_status varchar(3)  

set @current_status = ( SELECT approval_status from approval_master_tbl
        WHERE project_no = @project_no
       AND sequence_no = @sequence_no -1)

   if @approval = 'No' and @sequence_no = 1 
   begin 
       set @Approval_Status = 'DRA'
       set @post_approval_status = NULL
   end

   else if   @approval = 'No' and @sequence_no > 1 
   begin 
      set @Approval_Status = 'DRA'
      set @post_approval_status = ''
   end


   else if @approval = 'Yes' and @sequence_no > 1 and @current_status = 'DRA' 
   begin 
      set @Approval_Status = 'APP'
      set @post_approval_status = ''
   end
--ISSUE IS HERE!!!!
   else if @approval = Null and @sequence_no > 1 and @current_status = 'DRA' 
   begin 
      set @Approval_Status = 'APP'
      set @post_approval_status = ''
   end

   else if @approval = 'No' and @sequence_no > 1 and @current_status = 'APP'

   begin  
       set @Approval_Status = 'APP'
       set @post_approval_status = '' 
   end

   else if @ratify = 'YES' and @sequence_no > 1 and @current_status = 'APP'

   begin   
      set @Approval_Status = 'APP'
      set @post_approval_status = 'ARF'        
   end

   else if @approval = 'No' and @sequence_no > 1 and @current_status = 'APP' AND @post_approval_status = 'ARF'
   begin       

       set @Approval_Status = 'HIS'
       set @post_approval_status = 'ARF'                       
   end

   else if @approval = 'YES' and @sequence_no > 1 and @current_status = 'HIS' 
   begin 

      set @Approval_Status = 'HIS'
      set @post_approval_status = ''
   end
   else if @ratify = 'YES' and @sequence_no > 1 and @current_status = 'HIS' 
   begin  
      set @Approval_Status = 'APP'
      set @post_approval_status = 'ARF'
   end
SET NOCOUNT ON




   BEGIN TRANSACTION



   INSERT INTO [dbo].[approval_master_tbl]
          ([project_no]
          ,[sequence_no]
          ,[grant_programme]
          ,[jobs_approved]
          ,[approval_status]
          ,[committee]
          ,[meeting_no]
          ,[minute_item]
          ,[jobs_maintained]
          ,[comments]
          ,[approval_date]
          ,[approved_by]
          ,[post_approval_status]
          ,[create_date]
          ,[create_by]
          ,[update_date]
          ,[update_by]       
          )
    VALUES(
           @project_no
           ,@sequence_no 
           ,@grant_programme
           ,@jobs_approved
           ,@approval_status 
           ,@committee
           ,@meeting_no
           ,@minute_item
           ,@jobs_maintained
           ,@comments
           ,@approval_date
           ,@approval_by
           ,@post_approval_status
           ,@created_date
           ,@created_by
           ,@updated_date
           ,@updated_by)

--Update previous Approval status

IF @sequence_no > 1 

UPDATE approval_master_tbl
       SET approval_status = @post_approval_status
 WHERE project_no = @project_no
       AND sequence_no = @sequence_no - 1




--Update Project Status in project Table

   if @approval = 'YES' 

       UPDATE PROJECT_TBL
            SET    PROJECT_STATUS = 'YET'
                   ,DATE_FIRST_APPROVED = @approval_date
                   ,actual_jobs_approved = @jobs_approved 
                   ,actual_amt_approved = @grant_amount 
       WHERE PROJECT_TBL.project_no = @project_no 



           COMMIT TRAN
            SET @ReturnValue = 0


            END TRY

   BEGIN CATCH

          IF @@ERROR <> 0 
               BEGIN
                   ROLLBACK TRAN
                   DECLARE @ErrorMessage NVARCHAR(4000)=ERROR_MESSAGE()
                   DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
                   DECLARE @ErrorState INT = ERROR_STATE()

                   RAISERROR (@ErrorMessage,  
                              @ErrorSeverity,   
                              @ErrorState
                              );
                   SET @ReturnValue = 1
                   SET @ErrorMessage=ERROR_MESSAGE();
                   SELECT ERROR_MESSAGE() as ErrorMessage;
               END
       END CATCH           
   RETURN @ReturnValue         
END

這

@approval = Null

從不評估為 TRUE，它評估為 UNKNOWN。請參閱NULL 和 UNKNOWN。

應該

@approval is Null 

相反，@approval = Null 您應該將其更改為： else if (@approval IS NULL OR @approval = ’ ‘) and …..

通過這種方式，您將能夠評估 @approval 是 null 還是空字元串，因為您的參數是 varchar(5)，然後您也可以在某個時刻收到一個空字元串。

引用自：https://dba.stackexchange.com/questions/267014