CREATE PROCEDURE syntax:
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
[ ] (brackets) Optional syntax items. Do not type the brackets.
{ } (braces) Required syntax items. Do not type the braces.
And human readable form:
Let's try to write stored procedure with empty body:
CREATE PROC my_proc AS
-- please treat it as separate call, for example with different session
EXEC my_proc
is perfect valid syntax.
LiveDemo
So it looks like that sql_statement
could be empty.
Now let's try the same with but this time with BEGIN/END
block:
CREATE PROC my_proc AS
BEGIN
END
-- Incorrect syntax near 'END'.
LiveDemo2
Why is the first example valid? If sql_statement
allows nothing then second example should work too or the doc is inaccurate.
EDIT
well, that's because in the first example it isn't an empty body, your sp will be: EXEC my_proc
The case was to show that I could call SP. But you could add GO
or use EXEC
:
CREATE PROC my_proc AS
GO
EXEC my_proc
or
EXEC('CREATE PROC my_proc AS')
EXEC my_proc
LiveDemo3
The syntax error is not related to the proper syntax for stored procs. It is the proper syntax for "BEGIN/END". BEGIN/END requires some SQL inside of it to be valid. The documentation for BEGIN/END shows this:
https://msdn.microsoft.com/en-us/library/ms190487.aspx
BEGIN
{
sql_statement | statement_block
}
END
The grammar in the CREATE PROC documentation is indeed not fully correct, as it says that sql_statement is required for "CREATE PROC", when it is actually not required.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With