Stored Procedure doesnt return Identity

Question

Stored Procedure doesnt return Identity

Parro Tone

SSC Rookie

Points: 34
More actions
March 24, 2005 at 3:42 pm

#163994

I have a stored procedure that is supposed to archive a row from one table into an archive table and return the archived record ID. PROBLEM: If I do not pass the @@Identity or IDENT_CURRENT() variable the data is copied successfully to the archive table. However when I pass the @@Identity or IDENT_CURRENT() the data does not get copied to the archive table but I do get the appropriate new identity as a return value. Im a SQL newb so there must be something simple I'm missing.
Here's is a fascimile of my procedure:
CREATE PROCEDURE Archive
( @ CurrentRecordID int,
@ArchiveRecordID int OUTPUT
)
AS
SET NOCOUNT ON
INSERT INTO tblRecordsArchive
(column 1, column 2, column3)
SELECT column 1, column 2, column3
FROM tblRecords
WHERE (column 1 = @CurrentRecordID)
SELECT @ArchiveRecordID = IDENT_CURRENT('tblRecordsArchive')
RETURN

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Parro Tone SSC Rookie Points: 34 More actions · Answer 1

I should add that I am using MSDE and calling the procedure from ASP. The procedure appears to run fine in my access front end but does not work when called from ASP.

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

Why not to use the scope_identity right after the insert?

CREATE PROCEDURE Archive

( @ CurrentRecordID int,

@ArchiveRecordID int OUTPUT

)

AS

SET NOCOUNT ON

INSERT INTO tblRecordsArchive
(column 1, column 2, column3)
SELECT @ArchiveRecordID = scope_identity()
SELECT column 1, column 2, column3
FROM tblRecords
WHERE (column 1 = @CurrentRecordID)

Bye
Gabor

Parro Tone SSC Rookie Points: 34 More actions · Answer 3

Thanks but..I cant do that because I get an error saying I cant assign values to a variable within an INSERT statement.

This is really frustrating...no matter what I do to retrieve the @@Identity value wipes out the data copied to the archive table. If I dont try to access the @@Identity then it copies the data successfully. Ive even tried different recordset commands...one to trigger the archive procedure and then one to read the @@Identity but it still clobbers my data.

ron k SSCertifiable Points: 6103 More actions · Answer 4

First, I don't think you want to be using IDENT_CURRENT in this case becuse it will give you the latest identity value inserted for the specified table by any user and not necessarily the one created by your actions. It sounds like you need to return either @@Identity or Scope_Identity.

Second, there's a bit of confusion here (perhaps on my part). If the row did NOT get inserted how could you possible get back the "apropriate" identity value as none was created.

I think you should post the definitions of the tables involed and the exact code you are running.

Parro Tone SSC Rookie Points: 34 More actions · Answer 5

Point well taken on IDENT_CURRENT.

Problem Solved! I was running some code outside a sub that was deleting the newly created record. DUH!

Thanks for the assistance!