Wednesday, March 25, 2015

SQL Server 2008 - Get primary key in INSERT SQL statement

This tutorial is about to receive the primary key of a new inserted dataset.

declare @CreatedID bigint
exec sp_MyStoredProcedure parameter1, parameter2, @CreatedID OUTPUT
select @CreatedID

The stored procedure looks like this:
ALTER PROCEDURE [dbo].[sp_MyStoredProcedure]
@parameter1 int,
@parameter2 int,
@CreatedID BIGINT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

insert into 
tbl_MyTable
(
field1,
field2
)
values
(
@parameter1,
@parameter2
);
SELECT @CreatedID = CAST(SCOPE_IDENTITY() as bigint)
END

No comments:

Post a Comment