当我将 SQL 列更新为自身时会发生什么？ -或- 简化条件更新？
我想调用一个“更新”存储过程，它不一定包含所有列。 可能有更好的方法来处理这个问题...... 正如您所看到的，如果我不传入列参数，它们的值为 NULL。然后，使用 ISNULL，我将列设置为其新值或现有值。
CREATE PROCEDURE [dbo].[spUpdateTable]
@pPKID bigint = NULL,
@pColumn1 int = NULL,
@pColumn2 int = NULL
AS
BEGIN
SET NOCOUNT ON;
UPDATE
TableName
SET
[Column1] = ISNULL(@pColumn1,[Column1]),
[Column2] = ISNULL(@pColumn2,[Column2])
WHERE
[PKID] = @pPKID
END
I would like to call an "update" stored procedure which won't necessarily include all columns.
There is probably a better way to handle this....
As you can see, if I do not pass in the column parameters their value is NULL. Then, using the ISNULL, I set the columns either to their new values or their existing values.
CREATE PROCEDURE [dbo].[spUpdateTable]
@pPKID bigint = NULL,
@pColumn1 int = NULL,
@pColumn2 int = NULL
AS
BEGIN
SET NOCOUNT ON;
UPDATE
TableName
SET
[Column1] = ISNULL(@pColumn1,[Column1]),
[Column2] = ISNULL(@pColumn2,[Column2])
WHERE
[PKID] = @pPKID
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这基本上与事务复制存储过程在更新订阅者上的表时执行的操作相同。如果微软自己做的话，一定是安全的吧？ :-)
说真的，我在这里主要关心的是表上可能存在的任何更新触发器。您想要了解潜在触发这些触发器对可能不发生变化的影响。不然我觉得你的技术还是不错的。
This is basically the same thing that the transactional replication stored procedures do when updating a table on a subscriber. If Microsoft does it themselves, it must be safe, right? :-)
Seriously, my primary concern here would be any update triggers that might exist on the table. You'd want to understand the impact of potentially firing those triggers on what could be a non-change. Otherwise I think your technique is fine.