BEGIN TRANSACTION statement, where to place? 1

[Naoise]

I have a stored procedure of the general form below. Essentially 3 cursors which each perform INSERT
and UPDATE statements dynamically. If I wanted to add in a "BEGIN TRANSACTION" statement where would
I place this so that I don't get any of those
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
type errors. Should it be after the XACT_ABORT statement and then wrap all proceeding lines in a BEGIN and END? Should there
be a begin transaction statement inside each cursor?

CREATE PROCEDURE spMyProc
AS
BEGIN

SET XACT_ABORT OFF

CREATE TABLE #tblFoo ....

INSERT INTO #tblFoo ....
	
DECLARE @Cursor_Val1 INT
DECLARE @Cursor_Val2 VARCHAR(50)
DECLARE @Cursor_Val3 VARCHAR(50)
DECLARE @Cursor_Val4 VARCHAR(50)
DECLARE @Cursor_Counter INT

SET @Cursor_Counter = 0

-------------------------------------------------
-- CURSOR 1
-------------------------------------------------
DECLARE MyCursor cursor for

SELECT OrganisationRoleID, CurrentDepthID FROM #tblFoo

OPEN MyCursor 

FETCH NEXT FROM MyCursor INTO @Cursor_Val1, @Cursor_Val3
	
WHILE @@fetch_status = 0
	BEGIN
		SET @Cursor_Counter = @Cursor_Counter + 1
		SET @Cursor_Val2 = ....
		
		SELECT ...
		
		IF @Cursor_Val4 IS NOT NULL
			BEGIN
				UPDATE #tblFoo 
				...
			END
		ELSE
			BEGIN
				UPDATE #tblFoo 
				...
			END		

		SET @Cursor_Val4 = NULL
		
		FETCH NEXT FROM MyCursor INTO @Cursor_Val1, @Cursor_Val3
	END
	
CLOSE MyCursor
DEALLOCATE MyCursor

-------------------------------------------------
-- CURSOR 2
-------------------------------------------------
CREATE TABLE #tblBar ...

DECLARE MyCursor cursor for

SELECT * FROM #tblFoo

OPEN MyCursor 

FETCH NEXT FROM MyCursor INTO @Cursor_Val1, @Cursor_Val3, @Cursor_Val4
	
WHILE @@fetch_status = 0
	BEGIN
		
		UPDATE ...
		
		UPDATE ...

		FETCH NEXT FROM MyCursor INTO @Cursor_Val1, @Cursor_Val3, @Cursor_Val4
	END

CLOSE MyCursor
DEALLOCATE MyCursor

-------------------------------------------------
-- CURSOR 3
-------------------------------------------------
DECLARE MyCursor cursor for

SELECT ....

OPEN MyCursor 

FETCH NEXT FROM MyCursor INTO @Cursor_Val1, @Cursor_Val3, @Cursor_Val2, @Cursor_Val4
	
WHILE @@fetch_status = 0
	BEGIN
		UPDATE ...

		UPDATE ...

		FETCH NEXT FROM MyCursor INTO @Cursor_Val1, @Cursor_Val3, @Cursor_Val2, @Cursor_Val4
	END
	
CLOSE MyCursor
DEALLOCATE MyCursor

DROP TABLE #tblFoo

END
GO

 

[SQLDenis]

if you have BEGIN TRANSACTION you also need commit or rollback

BTW this is a bad idea to begin with, transactions should be as small as possible, cursors (especially 3!!) should not be placed in a transaction. Concurrance will suffer bigtime because of blocking

can't you use a set base solution?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[monksnake]

Yes, kill the cursors please.

<.

 

[SQLDenis]

It would be nice if I could spell concurrency right the first time ;-(

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Naoise]

So say you had a number of different dynamic update statements to perform in a cursor, how would you go about managing them in transactions?

 

[SQLDenis]

>>So say you had a number of different dynamic update statements to perform in a cursor


I use cursors only for maintenance routines

but to answer your question
begin tran right after set xact_abort
after every update check for @@error
if it is not 0 GOTO the rollback code

read this:

http://www.sommarskog.se/error-handling-II.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

I'm with dennis. Never under any circumstances use a cursor for an insert, update or delete statement. And honestly dynamically creating SQL code is a horrible idea as well from both a performance and a security view.

Since you don't show us what you are doing in the cursors, it is hard for us to show you the better way to do it. Cursors are the worst code you can write in t-sql. They will cause performance problems. You need to stop thinking of processing individual records and start thinking about how you can process a set of records.



"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

Don't you mean SET XACT_ABORT ON?

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[SQLDenis]

>>Don't you mean SET XACT_ABORT ON?

ROFL, didn't even notice the ON or OFF

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

<joke>

Nah, leave it off. It improves performance.

</joke>

< M!ke >
I am not a hamster and life is not a wheel.