Listing 1: Stored Procedure That Returns the Identity Field as an Output Parameter
CREATE PROCEDURE spEmployeesInsertCmd
(
    @LastName nvarchar(20),
    @FirstName nvarchar(10),
    @Title nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Identity int OUT
)
AS
INSERT INTO Employees(LastName, FirstName,
Title, Address, City, Region, PostalCode) VALUES (@LastName,
@FirstName, @Title, @Address, @City, @Region, @PostalCode)
SET @Identity = SCOPE_IDENTITY()