Transaction error - 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 2005 Forums

Transact-SQL (2005)

Transaction error

Author

Topic

netsurfer
Starting Member

5 Posts

Posted - 2008-06-02 : 12:40:40

Declare @ID int
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction

Insert into t1([name]) values('Sample')
SELECT @ID = @@identity

// IT FAILS HERE BECAUSE DOES NOT FIND INSERTED RECORD
Insert into t1Details([idt1],[number]) values (@ID,20)

COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK

END
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

But it fails inserting the details because does not find the record inserted before.
IS there any way to correct this?
Thanks in advance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-06-02 : 16:13:06

Humm, maybe try SCOPE_IDENTITY() instead of @@IDENTITY?

netsurfer
Starting Member

5 Posts

Posted - 2008-06-04 : 07:35:55

Thanks it worked with SCOPE_IDENTITY() :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources