December 13, 2011 at 9:40 am
In my project, I am having some SPs which is having multiple RETURN statements.
When i tried to implement the TRANSACTION in such SPs (i didnt not noticed that multiple RETURN statements), before my COMMIT statement, it is RETURNing , making an UNCOMMITTED transaction
And such problem could not be identified at parse time too, obviously.
Below are the example of my problem and my suspected solution too.
I wanted from you people this,
1. is it not the good practice to have multiple RETURN statements in an SP, or does it have any good purpose so that my ex-developers used it.
2. How about my suspecting solution?, is there any other good practice.
Appreciating your Helps.
--Problem
AS
BEGIN
BEGIN TRAN
/* Condition 1 */
RETURN
/* Condition 1 */
RETURN
/* Condition 1 */
RETURN
COMMIT TRAN
END
--Suspecting Solution.
CREATE PROC MySP
AS
BEGIN
BEGIN TRY
BEGIN TRAN
/* Condition 1 */
RAISERROR('ERROR',16,1)
/* Condition 1 */
RAISERROR('ERROR',16,1)
/* Condition 1 */
RAISERROR('ERROR',16,1)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
December 13, 2011 at 9:45 am
How are the Return statements being used? Are they returning error codes, or are they just aborting the procedure so no further code is run in it?
I generally avoid using Return, except in debugging. (Or in UDFs, but that's a different use.)
What fix is best will depend on how Return is being used.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2011 at 9:56 am
It is used Just to abort other scripts,
No codes are returned.
December 13, 2011 at 9:59 am
Then what you probably want is Try Catch and Raiserror, like your proposed solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2011 at 10:58 am
each exit is going to need a commit:
IF @condition =1
BEGIN
--do stuff (like you said...either raise an error or commit.
COMMIT TRAN --RaisError?
RETURN
END
IF @condition =2
BEGIN
--do stuff
COMMIT TRAN --RaisError?
RETURN
END
Lowell
December 13, 2011 at 11:19 am
The exits needing commits depends on what you're trying to do.
If a condition calls for rolling back on a failure, then raising an error, cutting over to the Catch block, and rolling back there, won't require a separate rollback/commit at all.
If the conditions are sequential instead of exclusive, then you don't need a commit at each one, just one at the end before the End Try statement.
It depends on what the specific code is designed to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply