Multiple RETURNs in an SP

Question

Multiple RETURNs in an SP

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

SqlServerPass SSCommitted Points: 1709 More actions · Answer 2

It is used Just to abort other scripts,

No codes are returned.

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

Lowell SSC Guru Points: 323740 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 5

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