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.
You can use OUTPUT INSERTED.[cols]
to fetch rows the statement has modified.
UPDATE BatchItems
SET [Status] = 'Processing'
OUTPUT INSERTED.*
WHERE [Status] = 'Queued'
Example;
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
If you're using SQL Server 2005 or above you can use the OUTPUT clause.
http://msdn.microsoft.com/en-us/library/ms177564(v=SQL.90).aspx
update BatchItems
set Status = 'Processing'
output inserted.*
where Status = 'Queued'
精彩评论