Does Every BEGIN require an END

Question

Does Every BEGIN require an END

YSLGuru

SSC-Insane

Points: 21661
More actions
June 17, 2021 at 7:06 pm

#3897691

Does every instance of BEGIN within your code require an END?
I've seen more than one piece of code, usually a Stored procedure made by the vendor who provides our accounting software, that has more instances of BEGIN than END. I always believed that every instance of BEGIN required an END. Is that not the case and if no when can you use BEGIN without a matching END?

Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

You claim to have 3rd party software that demonstrates that. Why do we need to provide an example? I've never seen such a thing but could be wrong. Post the code and let's see. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

I can think of only 1 case where a BEGIN doesn't have an END - BEGIN TRANSACTION. That requires a COMMIT or a ROLLBACK. Otherwise I agree with Jeff here that a BEGIN needs an END and if you have some code where this isn't the case, it would be interesting to see the code.

There MAY be other use cases, but it is very likely that it is SIMILAR to BEGIN TRANSACTION in that it has a different keyword for marking the "end" than the literal word "END".

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Here are a couple more that won't have a corresponding END.

BEGIN CONVERSATION TIMER;
BEGIN DIALOG CONVERSATION;
BEGIN DISTRIBUTED TRANSACTION;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Jeffrey Williams wrote:

Here are a couple more that won't have a corresponding END.
BEGIN CONVERSATION TIMER;
BEGIN DIALOG CONVERSATION;
BEGIN DISTRIBUTED TRANSACTION;

Just curious... have you actually ever used any of those?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Nope, just examples I found. A vendor system I support does use service broker and has conversations set up.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

K. Thanks for the feedback. Me neither, for the record.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21661 More actions · Answer 7

Thank you to everyone who took time to respond. My point of the post was to verify that every plain/vanilla Begin (a BEGIN that is not followed by something like Transaction or Conversation or Dialogue) does require a matching END. I wasn't saying you could get working code that did not follow this rule only that I had seen some code from our vendor that was written like this.

Turns out what I was sent yesterday to review didn't work. I took the vendors word that it did. We do not have access to the DB that this query was written for so I had no way to test it myself, only review it. I didn't post it because I didn't know if I could, we have an NDA with the vendor so I'm always hesitant to share anything from their product. I know there are tools for scrambling SQL code so as to hide any real world privacy data and to work-a-round NDA's but this was very generic question that wasn't specif to one query.

Thanks again

Kindest Regards,

Just say No to Facebook!

Chris Wooding SSCarpal Tunnel Points: 4555 More actions · Answer 8

I've used BEGIN DISTRIBUTED TRANSACTION. I've got a control table in one database that is kept in line with a copy in another database via a linked server.