christopher clark christopher clark - 2 months ago 8
SQL Question

If Else in SQL Stored Proc on a SET Parameter

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?

Answer

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.