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!
This question is related to
sql-server
stored-procedures
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)
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.
One Idea:
UPDATE tbl_ClientNotes
SET ordering=ISNULL(@ordering, ordering),
title=ISNULL(@title, title),
content=ISNULL(@content, content)
WHERE id=@id
ALTER PROCEDURE LN
(
@Firstname nvarchar(200)
)
AS
BEGIN
UPDATE tbl_Students1
SET Firstname=@Firstname
WHERE Studentid=3
END
exec LN 'Thanvi'
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
Source: Stackoverflow.com