Using UPDATE in stored procedure with optional parameters

Question

I have an SP like so (using SQL Server):

ALTER PROCEDURE [dbo].[sp_ClientNotes_update]     @id uniqueidentifier,     @ordering smallint = NULL,     @title nvarchar(20) = NULL,     @content text = NULL AS BEGIN     SET NOCOUNT ON;     UPDATE tbl_ClientNotes     SET ordering=@ordering, title=@title, content=@content     WHERE id=@id END 

I would like to only set the values if they are passed into the SP, i.e. not NULL. Can this be done?

This question seems to suggest the only way is using completely separate queries with conditionals, but for 3 optional parameters this would obviously be a nightmare!

Answer 1

Try this.

ALTER PROCEDURE [dbo].[sp_ClientNotes_update]     @id uniqueidentifier,     @ordering smallint = NULL,     @title nvarchar(20) = NULL,     @content text = NULL AS BEGIN     SET NOCOUNT ON;     UPDATE tbl_ClientNotes     SET ordering=ISNULL(@ordering,ordering),          title=ISNULL(@title,title),          content=ISNULL(@content, content)     WHERE id=@id END 

It might also be worth adding an extra part to the WHERE clause, if you use transactional replication then it will send another update to the subscriber if all are NULL, to prevent this.

WHERE id=@id AND (@ordering IS NOT NULL OR                   @title IS NOT NULL OR                   @content IS NOT NULL)