返回更新记录的 SQL Server 存储过程 [英] SQL Server Stored Procedure that returns updated records

问题描述

我正在尝试创建一个返回所有更新记录的存储过程.

I'm trying to create a stored proc that returns all records that are updated.

我的更新查询是这样的:

My update query is this:

UPDATE BatchItems 
SET [Status] = 'Processing' 
WHERE [Status] = 'Queued'

我只是不确定如何返回这些特定记录.

I'm just not sure how to return those specific records.

推荐答案

您可以使用 OUTPUT INSERTED.[cols] 获取语句修改过的行.

You can use OUTPUT INSERTED.[cols] to fetch rows the statement has modified.

UPDATE BatchItems 
   SET [Status] = 'Processing' 
   OUTPUT INSERTED.*
WHERE [Status] = 'Queued'

示例；

select 'Queued       ' as status, 1 as id into #BatchItems
insert  #BatchItems values ('Queued', 2)
insert  #BatchItems values ('XXX', 3)

UPDATE #BatchItems 
  SET [Status] = 'Processing' 
  OUTPUT INSERTED.*
WHERE [Status] = 'Queued'

>>status       id
>>Processing   2
>>Processing   1

这篇关于返回更新记录的 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！