How to have a transaction inside a cursor

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 2008 Forums

Transact-SQL (2008)

How to have a transaction inside a cursor

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-02 : 13:53:29

I am having a cursor
inside it i perform several insert operations
I would like to roll them back if one is not successful
how can i do that

sarah

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-02 : 14:25:29

Look in BOL (book online) for the BEGIN TRANSACTION statement and COMMIT or ROLLBACK TRANSACTION statements:
http://msdn.microsoft.com/en-us/library/ms188929.aspx

Though I have to ask, why are you using a cursor? Any chance this can be done without a cursor?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 20:36:11

if there's a set based equivalent you can think of that would be great. cursors does a row by row processing and perform poorly especially when dealing with large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-03 : 19:51:39

I know that cursors are not the best.
But i am insert a record into a table (user) with the following fields userid (identity) , name, etc
then get @@identity =new userid and insert another record in a related table userassociation (userid, orgid, etc)
then insert @@identity=new userid into another related table
I am doing that for all rows in a temporary table

Any idea how can i do that without using a cursor
Thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 00:10:36

yep. you can use OUTPUT clause to do this as example below


DECLARE @INSERTED_USERS
(
userid int
)
INSERT user (name,..)
OUTPUT INSERTED.userid INTO @INSERTED_USERS
VALUES('Tom',...)

INSERT userassociation (userid,orgid,...)
SELECT userid,'org 1',...
FROM @INSERTED_USERS

INSERT otherrelatedtable(userid,....)
SELECT userid,....
FROM @INSERTED_USERS
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-04 : 18:56:43

Thanks SO SO much

but now the @INSERTED_USERS is declared once
so each time a new row will be added
do I need to delete that row as soon as I finish

Do I need to use a while loop in this case

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 21:20:30

delete that row from where? its a table variable so once batch is completed it gets destroyed automatically hence next time you run the query batch it gets created again
no need of while loop as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources