Using UPDATE in stored procedure with optional parameters

Viewed 126230

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!

5 Answers
Related