在存储过程中使用带有可选参数的UPDATE

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

我有一个像这样的SP（使用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?

我想只在它们被传递到SP时设置值，即不是NULL。可以这样做吗？

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!

这个问题似乎表明唯一的方法是使用完全独立的条件查询，但对于3个可选参数，这显然是一场噩梦！

5 个解决方案

#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子句添加一个额外的部分，如果你使用事务复制，那么如果所有都是NULL，它将向订阅者发送另一个更新，以防止这种情况发生。

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

#2

   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.

我想我记得在此之前看到如果你更新到相同的值，SQL Server将实际识别这一点，并且不会进行不必要的写入。

#3

One Idea:

一个想法：

UPDATE tbl_ClientNotes
SET ordering=ISNULL(@ordering, ordering), title=ISNULL(@title, title),  content=ISNULL(@content, content)
WHERE id=@id

#4

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

或者，如果您的意思是您只想更新单个列，则可以使用我上面的帖子。我读它是因为如果任何值为null则不更新

#5

-2

ALTER PROCEDURE LN
    (
    @Firstname nvarchar(200)
)

AS
BEGIN

    UPDATE tbl_Students1
    SET Firstname=@Firstname 

    WHERE Studentid=3
END


exec LN 'Thanvi'

#1