0

I am having an issue with this stored procedure. When the user selects "Yes", it should do what it says in the if statement for the "Yes" but it does not do anything, it does not insert anything into the database. It works fine for the "No" condition. Could you please tell me what it is not working ?

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
6

This

@approval = Null

never evaluates to TRUE, it evaluates to UNKNOWN. See NULL and UNKNOWN.

Should be

@approval is Null 
3
  • Yes that's true but that does not answer the questions as why it does pick up "Yes" condition, the "Yes" option is being passed on from front-end but it does not do the insert in the above statement. – Rachel May 12 '20 at 13:54
  • To debug print or select all of the variables used in your control flow and check their values. – David Browne - Microsoft May 12 '20 at 14:18
  • 1
    Where you have your comment (the issue is here), you do a check for @variable = NULL, and this will always evaluate to UNKNOWN, which in turn will be treated as FALSE.Since you AND it with two others and this is FALSE, then the whole condition is FALSE. – Tibor Karaszi May 13 '20 at 6:48
0

Instead @approval = Null you should change it to: else if (@approval IS NULL OR @approval = ' ') and .....

In this way you will able to evaluate if @approval is null or an empty string because your argument is a varchar(5) and then you can receive in some moment a empty string too.

0

in addition to using the correct form

@approval is Null 

as advised by David Browne and by jmonterohn and also from Tibor Karaszi in the comments:

Where you have your comment (the issue is here), you do a check for @variable = NULL, and this will always evaluate to UNKNOWN, which in turn will be treated as FALSE.Since you AND it with two others and this is FALSE, then the whole condition is FALSE.

I've had a look at this and have being able to see another possible issue.

The stored procedure has an IF statement that results in one of two different select statements being run depending on the value of a parameter.

The problem with this is, that when the stored procedure first executes, one of the two select statements will be used to cache the execution plan. The result of this is, that if the other select statement then needs to run under another execution, the wrong execution plan will be used.

If you mark the stored procedure for recompilation (or use option recompile or optimize for unknown) you will be able to confirm that execution of the stored procedure (either from ssms or from an application) would be in much less execution time.

However, a better long-term solution would be to modify the stored procedure by moving the two branches of the IF statement into separate stored procedures.

For example:

IF CHARINDEX('%',@strPostalCode) = 0
BEGIN
insert into @cs EXEC @new_procedure_1
END
ELSE
       insert into @cs EXEC @new_procedure_2
END

The benefit of doing this is that each of the 2 new procedures will have their own execution plans.

Hope this is of use to someone, I have had issues with this in the past.

Not the answer you're looking for? Browse other questions tagged or ask your own question.