Insert a Record Using a Stored Procedure and Return Newly Added Identity ID« View all SQL articles
March 24, 2007
There are many times that you need to retrieve a newly added record's IDENTITY field. You can retrieve the ID value using the
SCOPE_IDENTITY() function.
This example is using the Northwind database. The following stored procedure takes 5 parameters and inserts them into the Supplier table. It also returns the ID of the newly added record.
CREATE Procedure InsertSupplier
( @CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Country nvarchar(15)
)
AS
INSERT INTO [Northwind].[dbo].[Suppliers]
([CompanyName], [ContactName], [ContactTitle], [Address], [City], [Country])
VALUES
(@CompanyName, @ContactName, @ContactTitle, @Address, @City, @Country);
SELECT SCOPE_IDENTITY() as SupplierID --this returns the newly added IDENTITY ID.
GO
|