Will SCOPE_IDENTITY Work in this Case?

Question

I have PK that is self incrementing key. I need to insert the record into the database and then get that PK back and use it in another insert.

However I would like to do this in one transaction. Is that possible. The idea is that if something fails in any of the updates/inserts I have to do then I can rollback everything but I am under the impression that I need to do a commit.

I was going to do it in ado.net at first but then switched to a stored procedure since I thought maybe that would get around this issue.

Will a SP help me out in this case?

Answer 1

Yes, scope_identity will give you the latest inserted id. As an alternative, if you're using sql server 2005+ you can use the output clause.

INSERT INTO [MyTable]([MyCol])
OUTPUT INSERTED.ID
SELECT [MyCol] FROM [MySourceTable];

Answer 2

How about:

BEGIN TRANSACTION
BEGIN TRY

   INSERT INTO dbo.YourFirstTable(.....)
   VALUES(.......)

   DECLARE @newID INT
   SELECT @newID = SCOPE_IDENTITY()

   INSERT INTO dbo.YourSecondTable(ID, .......)
   VALUES(@newID, ........)

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    ROLLBACK TRANSACTION
END CATCH

Should work in any version of SQL Server 2005 or newer.

Just by fetching the SCOPE_IDENTITY() value, you're definitely not "breaking" the transaction ... wrap this into e.g. a stored procedure, or just call it from your calling code.