CODEWITHSUNDEEP
sqlsql-servert-sql
José Rodríguez
José Rodríguez

Reputation: 13

T-SQL: Update statement within a stored procedure

I have a stored procedure that updates the data of table. The stored procedure has optional parameters with NULL default values, like this:

@parameter1 datatype = NULL,
@parameter2 datatype = NULL,
@parameter3 datatype = NULL.

I need the stored procedure to update the column only if the corresponding parameter is not NULL. I was thinking of doing the following:

IF @parameter1 IS NOT NULL
    UPDATE Table 
    SET column1 = @parameter1 
    WHERE id = @id;

IF @parameter2 IS NOT NULL
    UPDATE Table 
    SET column2 = @parameter2 
    WHERE id = @id;

IF @parameter3 IS NOT NULL
    UPDATE Table 
    SET column3 = @parameter3 
    WHERE id = @id;

I don't know how efficient this solution would be since there are 42 columns that need to be updated this way. I wonder if there is a more efficient solution.

Upvotes: 1

Views: 814

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

What if you can do ?

UPDATE Table 
      SET column1 = coalesce(@parameter1, column1),
          column2 = coalesce(@parameter2, column2), 
          column3 = coalesce(@parameter3, column3)
WHERE id = @id;

Upvotes: 7

Related Questions