Stored Procedures - How to use OUTPUT clause to retrieve new identity values from SQL Server 2008

SQL Server 2005 introduced the OUTPUT clause in DML statements.
We can use this in an INSERT ... SELECT statement to capture all the inserted identity values.

Create an Employee table

CREATE TABLE [dbo].[tbl_employee]
  (
     [serial]      [INT] IDENTITY(1, 1) NOT NULL,
     [empname]     [VARCHAR](50) NULL,
     [empsal]      [VARCHAR](50) NULL,
     [emplocation] [VARCHAR](50) NULL
  ) 

Create a stored procedure to get the identity value

CREATE PROCEDURE [dbo].[Temp]
AS
  BEGIN
      -- Declare table variable to hold results
      DECLARE @Temp TABLE
        (
           serial INT
        )

      -- Insert into table with IDENTITY column      
      INSERT INTO [Temp].[dbo].[tbl_employee]
                  ([empname],
                   [empsal],
                   [emplocation]) 

      -- Use OUTPUT clause to retrieve new value 
      output      inserted.serial
      INTO @Temp(serial)
      VALUES      ('emp1',
                   '15000',
                   'Hyderabad')

      --Select the identity value
      SELECT serial
      FROM   @Temp
  END  

If you execute the above stored procedure you will get the identity of the last inserted row.

EXEC [dbo].[Temp]  

Serial
------------------------------------------------------------
  1

EXEC [dbo].[Temp]  

Serial
------------------------------------------------------------
  2

EXEC [dbo].[Temp]  

Serial
------------------------------------------------------------
  3