Are BEGIN / END keywords required in a stored procedure?

Question

Possible Duplicate:
When do I need to use Begin / End Blocks and the Go keyword in SQL Server?

Example:

CREATE PROCEDURE DoSomething
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert lots of statements in here, including other stored procedures.
END

Do you need the BEGIN and END? Does it make ANY difference if you have them or not?

if I have multiple statements inside the "BEGIN" .... what if an "exception" happens for ONE of them and I get an error. Will it continue executing the other lines of code or will it exit the "BEGIN?" — JJ., Oct 02 '12 at 20:36
No it counts the rest of the batch as the stored proc definition (even after the `END` if a `BEGIN ... END` is present). So it is quite easy to inadvertantly leave a recursive `EXEC` call in there! — Martin Smith, Oct 02 '12 at 20:37
In `IF` statements and loops, `BEGIN ... END` is good practice. In stored procedures, it should be considered bad practice; it gives a false sense of encapsulation. This is explained [here](https://stackoverflow.com/questions/4188557/statements-after-end-in-stored-procedure). — Ruud Helderman, Jun 16 '17 at 11:17

score 15 · Answer 1 · edited Jun 20 '20 at 09:12

15

They are optional.

From MSDN - CREATE PROCEDURE:

From the definition of the command -

AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

And later on in the page:

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

One or more Transact-SQL statements comprising the body of the procedure. You can use the optional BEGIN and END keywords to enclose the statements.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 02 '12 at 20:36

Oded

489,969
99
883
1,009

score 7 · Answer 2 · answered Oct 02 '12 at 20:37

As indicated in the CREATE PROCEDURE documentation, they are optional.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

One or more Transact-SQL statements comprising the body of the procedure. You can use the optional [emphasis added] BEGIN and END keywords to enclose the statements. For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

Personally, I always include them, but that's just me.

Are BEGIN / END keywords required in a stored procedure?

2 Answers2