Will SCOPE_IDENTITY work in this case?

Question

I have PK, which is the key to increase. I need to insert a record into the database and then return it back to another.

However, I would like to do this in one transaction. It is possible. The idea is that if something fails in any updates / inserts that I have to execute, I can roll back everything, but I get the impression that I need to commit.

At first, I was going to do this in ado.net, but then switched to a stored procedure, since I thought that this could work around this problem.

Will SP help me in this case?

Answer 1

Yes, scope_identity will provide you with the last identifier inserted. Alternatively, if you are using SQL Server 2005+, you can use the sentence.

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

Answer 2

What 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 

It should work in any version of SQL Server 2005 or later .

Just by choosing SCOPE_IDENTITY() , you are definitely not breaking the transaction ... wrap this, for example. stored procedure or just call it from your call code.