Execute the following T-SQL script in Query Editor to create and apply a a stored procedure which captures the identity just created by the INSERT statement on the identity(1,1) surrogate key column using the SCOPE_IDENTITY() function, an alternate to @@IDENTITY.

USE AdventureWorks

GO

-- SQL Server stored procedure - Primary key identity insert

CREATE PROC [Production].uspInsertLocation

           @LocationID   INT  OUTPUT,

           @Name         CHAR(30),

           @CostRate     SMALLMONEY,

           @Availability DECIMAL(8,2)

AS

  BEGIN

    SET NOCOUNT  ON

    --Error Handling Start

    DECLARE  @ReturnValue INT

    SELECT @ReturnValue = 0

    -- Insert

    INSERT INTO [AdventureWorks].[Production].[Location]

               ([Name],

                [CostRate],

                [Availability],

                [ModifiedDate])

    SELECT @Name,

           @CostRate,

           @Availability,

           getdate()

    --Error Handler Catch

    SELECT @ReturnValue = @@ERROR

    SET @LocationID = scope_identity()

    EXITPOINT:

    RETURN @ReturnValue

  END

GO

-- SQL execution script

DECLARE  @LocationID INT

EXEC [Production].uspInsertLocation

  @LocationID OUTPUT ,

  'Paint Dryer Room' ,

  5.0 ,

  90.00

SELECT [New ID Assigned] = @LocationID

GO

/* Result

New ID Assigned

61

*/