If Else in SQL Stored Proc on a SET Parameter

Question

I have this Stored Procedure below. The IF/ELSE examples that I could find in MSDN and so forth do not have IF/ELSE capabilities on the "SET" Parameter.

BEGIN
        UPDATE c_section_status
        -- put in if else statement here to update page has changed or not.
        SET     status_ID = @status_id,
                status_date = GETDATE(),
                page_has_not_changed = @page_has_not_changed
            /*------------------------------------------------------------------*/
            -- log data to history table
            OUTPUT INSERTED.cppsa_ID,INSERTED.menuitem_ID
                ,INSERTED.status_ID,
                INSERTED.page_has_not_changed,
                INSERTED.status_date
                ,@user,GETDATE(),0
            INTO c_section_status_HIST
            /*------------------------------------------------------------------*/
        WHERE   cppsa_id = @cppsa_id
                and menuitem_id = @menuitem_id
    END

So I want to Set the status_date = GETDATE() only if the page has changed. If the @page_has_not_changed is equal to 1 Then I want to keep the old status_date and not update it.

Is this possible?

Horaciux · Accepted Answer · 2016-09-29 19:31:27Z

1

You should use CASE statement

BEGIN
        UPDATE c_section_status
        -- put in if else statement here to update page has changed or not.
        SET     status_ID = @status_id,
                status_date = CASE WHEN @page_has_not_changed=1 THEN status_date  ELSE GETDATE() END,
                page_has_not_changed = @page_has_not_changed
            /*------------------------------------------------------------------*/
            -- log data to history table
            OUTPUT INSERTED.cppsa_ID,INSERTED.menuitem_ID
                ,INSERTED.status_ID,
                INSERTED.page_has_not_changed,
                INSERTED.status_date
                ,@user,GETDATE(),0
            INTO c_section_status_HIST
            /*------------------------------------------------------------------*/
        WHERE   cppsa_id = @cppsa_id
                and menuitem_id = @menuitem_id
    END

Notice that CASE statement comes in two flavors:

--Simple CASE expression:   
CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END   
--Searched CASE expression:  
CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

The simple CASE expression compares an expression to a set of simple expressions to determine the result.
The searched CASE expression evaluates a set of Boolean expressions to determine the result.

edited Sep 29, 2016 at 19:31

answered Sep 29, 2016 at 18:52

Horaciux

6,5152 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

christopher clark Over a year ago

Okay. Would I not be worried that Set status_date = status_date wouldn't know what date to give it? since it's not an input parameter in the proc. Wouldn't i need another select statement in the Case statement to get the existing date?

Horaciux Over a year ago

@christopherclark it would store the same value it has before

christopher clark Over a year ago

Then this is the best answer, it is the least verbose. But I want to keep the old date if @page_has_not_changed = 1. If you make that change this will be marked as the correct answer

Mike Davis · Accepted Answer · 2016-09-29 18:39:15Z

1

I often just do something like this in stored procs when I am trying to update a variable.

if (@page_has_not_changed= 0 ) 
        Begin
            --Do Something
        End
if (@page_has_not_changed = 1) 
        Begin
            --Do Something Else                 
        End

answered Sep 29, 2016 at 18:39

Mike Davis

951 gold badge2 silver badges10 bronze badges

1 Comment

christopher clark Over a year ago

This is also what I looked into. Would be more verbose for large queries.

christopher clark · Accepted Answer · 2016-09-29 19:50:27Z

1

just adding an IF condition before the UPDATE statement should work:

 IF @page_has_not_changed = 0
    BEGIN
        UPDATE c_section_status
        SET     status_ID = @status_id,
                status_date = GETDATE(),
                page_has_not_changed = @page_has_not_changed
            /*------------------------------------------------------------------*/
            -- log data to history table
            OUTPUT INSERTED.cppsa_ID,INSERTED.menuitem_ID
                ,INSERTED.status_ID,
                INSERTED.page_has_not_changed,
                INSERTED.status_date
                ,@user,GETDATE(),0
            INTO c_section_status_HIST
            /*------------------------------------------------------------------*/
        WHERE   cppsa_id = @cppsa_id
                and menuitem_id = @menuitem_id

    END

edited Sep 29, 2016 at 19:50

christopher clark

2,1436 gold badges32 silver badges50 bronze badges

answered Sep 29, 2016 at 19:22

Esxcf

113 bronze badges

1 Comment

christopher clark Over a year ago

I still need to make a change to @page_has_not_ changed, since the previous year could have been a 0, but the new year is 1. But yes, this appears it could work.

Collectives™ on Stack Overflow

If Else in SQL Stored Proc on a SET Parameter

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related