Select Identity of inserted row in sproc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Identity of inserted row in sproc

Author

Topic

tedmanowar
Starting Member

31 Posts

Posted - 2007-05-11 : 06:00:01

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...

TedManowar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 06:12:02

use scope_identity() function

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 06:18:12

Read about @@identity and scope_identity in sql server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 06:19:39

Read about @@identity and scope_identity in sql server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 06:24:31

Mladen you are fast and my internet is too slow

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 07:09:41

upgrade your internet

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-11 : 07:15:07

Select scope_identity() as ProjectId
from Projects
and also keep the code as as minimum as possible..
u have two insert statement but only the value changes...write like

IF @changeOrderId = 0
Set @changeOrderId = NULL
BEGIN
INSERT INTO [Projects]
([ProjectName],[ProjectDescription],[OrganizationId],[ChangeOrderId])
VALUES
(@ProjectName,@ProjectDescription,@OrganizationId, @ChangeOrderId)
END

here by u can reduce no. of lines

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-11 : 07:24:50

In v2005 you can return a resultset from an insert using the output clause which is handy if you are inserting more than one row.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources