Try Catch - 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)

Try Catch

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2008-05-13 : 09:03:14

Hi!

I'm trying to use Try Catch together with transaction handling but it seems like it doesn't do Rollback although I produce an error.

It looks something like this:


DECLARE @soID INT 
BEGIN TRY 
	BEGIN TRANSACTION 
		UPDATE Serviceobjekt 
		SET ServiceobjektstypID = 13		
                WHERE ServiceobjektID = 26555

                UPDATE .... 2 more tables

		INSERT INTO Serviceobjekt (
			Namn...)
                VALUES ('XXXX')
              
                SET @soID = @@IDENTITY 
 		INSERT INTO Atgard (
			Namn, ServiceobjektID)
                 VALUES ('sssss',@soID)

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

I get after changing some id's in the where clause which I know is wrong I get a result like this:

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

Shouldn't it be 0 rows affected on all if just one update or insert statement is wrong?

/Magnus

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-13 : 09:13:05

Are you seeing data inserted or updated in the respective tables? Rows affected message does not indicate that data was successfully committed to the table. It is status message shown for each statement.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 09:15:03

Another sidenote:

If you are writing for multiple user connected at the same time, use SCOPE_IDENTITY() in favor of @@IDENTITY.

@@IDENTITY will get you last inserted identity value, regardless of user and table, regardless of manual insert or trigger insert.
SCOPE_IDENTITY() will get you last inserted identity value in YOUR scope.

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-05-13 : 09:22:25

Thanks! You're right. I checked all tables and nothing has been either update or inserted.

/Magnus

Rauken
Posting Yak Master

108 Posts

Posted - 2008-05-13 : 09:23:15

Thanks for the advice using SCOPE_IDENTITY().

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources