How to update columns only if argument is equal to specific value?

Question

I have query that should update columns only if argument is equal 1. Here is example:

<cfquery name="updateQry" datasource="test">
    UPDATE Table1
    SET fname = form.fname,
        lname = form.lname,
        <cfif form.status eq 1>
            title = form.title,
        </cfif>
        last_update_dt = getDate()
    WHERE rec_id = form.record_id
 </cfquery>

I would like to move this SQL query to stored procedure. How I can achive the same process with SQL code only? Instead of <cfif form.status eq 1> title = @title,</cfif> how that can be replaced with SQL code? I use Sybase database and ColdFusion 2016. This code above should be moved to stored procedure.

Answer 1

I think you want a case expression, something like this:

UPDATE Table1
SET fname = form.fname,
    lname = form.lname,
    title = (case when form.status = 1 then form.title else table1.title end),
    last_update_dt = getDate()
WHERE rec_id = form.record_id;

In a stored procedure, you would probably use parameters:

UPDATE Table1
SET fname = @fname,
    lname = @lname,
    title = (case when @status = 1 then @title else table1.title end),
    last_update_dt = getDate()
WHERE rec_id = #record_id;