SQL Server: sql batch

Some rules

execute statement

If the first statement of a batch calls a stored procudure, the execute statement is not required.

Combining create statements

Some object creation statements cannot be combined with other creation statement and need to be the first statement in a batch. Those are:

create default
create function
create procedure
create rule
create schema
create trigger
create view

Note that the create table statement is absent from this list!

The following works:

create procedure p2 as print 'p2';
execute p2;
go

The following does not work, it throws the error message MSG 111, Level 15, State 1… 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch:

drop   procedure if exists p2;
create procedure p2 as print 'p2';
execute p2;
go

This does work again:

drop   procedure if exists p2;
create procedure p2 as print 'p2';
execute p2;
go

This also works

drop   procedure if exists p2;
create procedure p2 as print 'p2';
execute p2;
go

set statements

The two set statements set showplan_text and set showplan_all can only by the only statement in a batch.

SQL handle

The text of each SQL batch is uniquely identified by the text's MD5 message digest. This value is a varbinary(64) and referred to as an SQL handle.

The text itself is stored in the SQL Manager Cache (SQLMGR) and can be queried with the SQL handle from sys.dm_exec_sql_text.