SQL Server stored procedure returning updated records

Question

I am trying to create a saved process that returns all updated records.

My update request:

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

I just don’t know how to return these specific entries.

Answer 1

You can use OUTPUT INSERTED.[cols] to select the rows that the statement changed.

 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 

Answer 2

If you are using SQL Server 2005 or higher, 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'