|
Execute the following
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.
USE AdventureWorks
GO
CREATE PROC [Production].procInsertLocation
@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
-- execution script
DECLARE @LocationID int
EXEC [Production].procInsertLocation @LocationID OUTPUT, 'Paint Dryer Room', 5.0, 90.00
SELECT [New ID Assigned] = @LocationID
|