OUTPUT Command in SQL Server 2005

The output parameter that can be used in stored procedures. This is about returning effected data on a table with a few feature in SQL Server 2005.
SQL Server 2000

A simple question for you. If you want to retrieve last inserted identity value what do you do? Obviously SCOPE_IDENTITY() or @@IDENTITY will be your answer. There is a small different between these too, which I am not going to discuss right now. Even though both will satisfy the current requirement, I will use SCOPE_IDENTITY(), which is the correct one.

CREATE TABLE TempTable
(
ID INT IDENTITY(1 , 1)
, Code VARCHAR(25)
, Name VARCHAR(50)
, Salary Numeric(10 , 2)
)
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A001' , 'John' , 100 )
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A002' , 'Ricky' , 200 )

SELECT SCOPE_IDENTITY() AS LastInsertID

However, this will only valid when you need the last inserted ID. A Problem arises when you need the last updated or deleted data. In SQL Server 2000, you don't have any other option other than writing a trigger or triggers to capture them via inserted and/or deleted tables.

more...