Rollback not working on insert

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)

Rollback not working on insert

Author

Topic

Ms.Longstocking
Starting Member

9 Posts

Posted - 2008-05-27 : 21:23:00

Hello,
I have a stored procedure that updates my table from values entered in a datatable in my windows app.

An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case. Here is my code, where am I going wrong?

ALTER PROCEDURE [dbo].[spUploadUser]
(@userid varchar(10), @username varchar(50), @userstatus varchar(20))
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ERROR_STATE INT;
	BEGIN TRANSACTION
	INSERT INTO userprofile (uid, uname, ustatus)
	VALUES @userid, @username, @userstatus;
	SELECT @ERROR_STATE = @@ERROR;
	IF (@ERROR_STATE <> 0)
		BEGIN
			ROLLBACK TRANSACTION
			RETURN -1
		END
	ELSE
		COMMIT TRANSACTION
END

Regards,
MizPippz

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-28 : 02:33:15

The transaction is wrapped around each insert individually. If one fails, that insert will rollback but previous ones you've inserted will stay (because their transdactions committed successfully.)

If you want all or nothing (all the inserts ot none of the inserts) then the trasnaction needs to be started before you insert the first row and only committed if all of the inserts have succeeded.

--
Gail Shaw

Ms.Longstocking
Starting Member

9 Posts

Posted - 2008-05-28 : 11:55:08

then where does the "BEGIN TRANSACTION" go in the procedure?
outside the "BEGIN"?

I'm having an awful time with the required syntax.

Ms.Longstocking
Starting Member

9 Posts

Posted - 2008-05-28 : 15:09:25

I'm dying here. Can anyone not add to this thread?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 15:14:36

You have coded your transaction correctly. Your stored procedure is acting on one row at a time though. So any previous inserts that did not error will still be in the database after a rollback of a specific row. This is because you are not using a transaction around the entire batch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Ms.Longstocking
Starting Member

9 Posts

Posted - 2008-05-28 : 15:21:34

I'm with you so far. However, I'm having syntax issues on how to encompass the entire batch. Can you show me a skeleton of how it should be done?

Regards,
MizPippz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 15:23:41

Well you'd have to submit the inserts as a batch rather than one row at a time. I don't have enough knowledge about datatables to help with any specific code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-05-29 : 04:28:22

Personally I would get rid of the commit within the SP and do it at the client end outside of your loops. Only the client knows which SP calls need to be contained within the transaction

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-29 : 11:45:39

Actually, the Transaction in this context is irrelevant as an Insert is already atomic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources