Retrive identity - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Retrive identity

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-04-07 : 10:14:40

I want to retrieve the identity created when I insert a record into my sql table and assign its value to a second field as well as the key - don't ask why ! Here's my sp which updates my table but puts null in CallNo instead of the identity.

CREATE PROCEDURE spSL_AddSLCall

@strArea nvarchar(10),
@strTitle nvarchar(8),
@strSurname nvarchar(50),
@strCalldate smalldatetime,
@strAdd1 nvarchar(200),
@strAdd2 nvarchar(200),
@strAdd3 nvarchar(200),
@strAdd4 nvarchar(200),
@strPostcode nvarchar(10),
@strCalldesc nvarchar(4000),
@strCallnotes nvarchar(4000),
@strDivision nvarchar(100),
@strService nvarchar(100),
@strCategory nvarchar (100),
@strCallNo int OUTPUT

AS

INSERT INTO tblSL_Calls
(Area,
Title,
Surname,
Call_date,
Add1,
Add2,
Add3,
Add4,
PostCode,
Call_desc,
Call_notes,
Division,
Service,
CallNo
)

VALUES
(
@strArea,
@strTitle,
@strSurname,
@strCalldate,
@strAdd1,
@strAdd2,
@strAdd3 ,
@strAdd4,
@strPostcode,
@strCalldesc,
@strCallnotes,
@strDivision,
@strService,
@@Identity)

SELECT @strCallNo = @@Identity

RETURN @strCallNo
GO

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-07 : 10:28:35

You have to do it in 2 steps. First, insert the record. Then do an update after the insert because it is only during this time that there will a value in @@identity.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-04-07 : 10:37:39

That's what I've had to do. Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-07 : 11:46:49

If it's always the same as the identity column and doesn't need to be updatable you could also make it a calculated column.
ie:

create table #temp (rowid int identity(1,1), rowidcopy as rowid, col1 int)
insert #temp (col1) values (1)
SElect * from #temp

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources