Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server stored procedure with empty body

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:

enter image description here

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

like image 574
Lukasz Szozda Avatar asked Jun 03 '16 17:06

Lukasz Szozda


1 Answers

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.

like image 180
GendoIkari Avatar answered Nov 06 '22 16:11

GendoIkari