SQL Server Performance Forum – Threads Archive

Select Identity of inserted row in sproc

Hello, I need to create a stored procedure that inserts a row and returns the identity of the inserted row. The code that follows is just to show you the logic of what I need and doesn’t of course demonstrate a correct way of doing it. USE [Product_Orders]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Create_Project_ID]
@projectName varchar(max),
@projectDescription varchar(max),
@organizationId binary(15),
@changeOrderId int
AS
BEGIN
SET NOCOUNT ON; IF @changeOrderId = 0
BEGIN
INSERT INTO [Projects]
([ProjectName],[ProjectDescription],[OrganizationId],[ChangeOrderId])
VALUES
(@ProjectName,@ProjectDescription,@OrganizationId, null)
END
ELSE
BEGIN
INSERT INTO [Projects]
([ProjectName],[ProjectDescription],[OrganizationId],[ChangeOrderId])
VALUES
(@ProjectName,@ProjectDescription,@OrganizationId, @ChangeOrderId)
END Select MAX(ProjectId) as ProjectId
from Projects END Can anyone help on how to do it the right way ? Thanks in advance…

Read about @@identity or scope_identity() in sql server help file Madhivanan Failing to plan is Planning to fail

… before you wonder where the help file is, it is nothing books online that will be installed alongwith SQL Server component on your machine.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

You should also read IDENT_CURRENT. There is explained by means of an example, why SCOPE_IDENTITY() is mostly preferrable to the other two functions. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Btw, just realized that you’re on SQL Server 2005. Check the OUTPUT clause explanation in BOL as well. Added the clause word here.
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

OUTPUT being the syntax that lets you access the "inserted" and "deleted" snapshots from within the stored procedure?

Check out the OUTPUT clause explanations in BOL. On SQL Server 2005 you can do something like this just fine:
USE tempDB
GO
IF OBJECT_ID (‘dbo.t’) IS NOT NULL
DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t
( MyID INT IDENTITY (2, 2000)NOT NULL,
SomeColumn VARCHAR(20) NOT NULL,
);
GO INSERT INTO t (SomeColumn)
OUTPUT INSERTED.MyID
SELECT ‘Frank’ UNION ALL
SELECT ‘Kalis’; MyID
———–
2
2002 (2 row(s) affected)
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

]]>