Thread: BEGIN END useage Stored Procedures

What's the meaning of the BEGIN...END statements in Stored Procedures?

At the moment, I just have a BEGIN at the start and an END at the finish, but I've seen several examples where BEGIN..END is used inside IF..THEN statements

Code:

If(condition=true) THEN
BEGIN
....
END
ELSE
BEGIN
....
END
END IF;

Whilst I was googling the use of BEGIN..END I then discovered this thread albeit about MSSQL, I presume, where several posts say they don't use them at all.

What is the advantage / disadvantage of using them and what is the most appropriate way to use them? One block or seperated by conditions?

BEGIN..END is most used in IF statements. You can think of it exactly like the { } in C# or like IF...THEN...END IF in BASIC. Basically, the BEGIN...END sets off multiple SQL statements to be executed as a block if the condition is TRUE (in an IF statement for example).

While you can use BEGIN..END around the entire stored procedure, but there is really no need in this case.

Note that this is true for T-SQL and is probably close to ASNI SQl, so MySql could be different. For example, there is no END IF statement in T-SQL.

Thanks Wolffy....Is there any advantage over:-
a) using BEGIN...END statements in the first place? See link
b) using seperate BEGIN...END statements for each condition?

IE, is there any advantage (performance or otherwise) in using

Code:

IF
BEGIN
.
END
ELSE
BEGIN
.
END
END IF

over

Code:

BEGIN
IF
.
ELSE
.
END IF
END

I can see no advantage of using a BEGIN..END around the entire procedure -- I'd call that a matter of personal preference. I then to put a BEGIN after I declare my procedure variables, but there is no syntactical reason to do so.

The BEGIN..END is required for code blocks in the IF statement, unless the code block has only a single statement; as in your first example. Now, MySql may have extended ANSI to allow the IF...ELSE...END IF syntax (don't KNOW that, don't use MySql). However, I'd consider sticking to the ANSI standard where possible in case you ever need to migrate from MySql to MSSQL Server.

In your second example, the BEGIN..END servers no purpose that I can see.