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

User · Answer

UPDATE tbl ClientNotes     SET        ordering ISNULL ordering ordering          title isnull  title title          content isnull  content content      WHERE id  id   I think I remember seeing before that if you are updating to the same value SQL Server will actually recognize this and won t do an unnecessary write

User · Answer

UPDATE tbl ClientNotes  SET ordering  ordering  title  title  content  content  WHERE id  id  AND  ordering IS NOT NULL AND  title IS NOT NULL AND  content IS NOT NULL   Or if you meant you only want to update individual columns you would use the post above mine  I read it as do not update if any values are null

User · Answer

ALTER PROCEDURE LN            Firstname nvarchar 200     AS BEGIN      UPDATE tbl Students1     SET Firstname  Firstname       WHERE Studentid 3 END   exec LN  Thanvi

User · Answer

One Idea   UPDATE tbl ClientNotes SET ordering ISNULL  ordering  ordering        title ISNULL  title  title         content ISNULL  content  content  WHERE id  id

User · Answer

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

[sql-server] Using UPDATE in stored procedure with optional parameters

Examples related to sql-server

Examples related to stored-procedures