9

I cannot find a list of type of objects that while being created the command must be the first statement in query batch. So far I ve found that function/stored_procedure/view must be the first.

Does anyone know why this is required? Im sure there is a good reason, but it escapes me. The only reason for stored procedures that comes to mind is that weird interpretation of code behind END of the procedure. The SQL server still executes the code when the SP is called and had there been any more CREATE STATEMENTS the result would be mess.

Background: I m trying to create create/drop/disable/enable etc. scripts and need to know what to put into dynamic exec() for before execution. Yes I might wrap all statements in exec(), but then I would not learn anything.

1 Answer 1

12

From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

Rules for Using Batches

The following rules apply to using batches:

  • CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
  • A table cannot be changed and then the new columns referenced in the same batch.
  • If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.
3
  • 4
    Thank you for the list. Too bad I still cant see why is it required. Particularly with tables, you can create table and then insert into it yet you cant add new column and then reference it. I guess "no plan" is acceptable (table does not yet exist), but incorrect one is not (table with "different" schema exists. Feb 13, 2013 at 14:32
  • 2
    Sorry, can't help you with that. These rules are probably something what is a causality of the way the system works, rather than something which is made intentionally. Creating tables has to be supported because you must be able to create temporary tables and manipulate them in a single batch. So, that feature is something you have to "plan for". Adding columns to existing tables in a batch is probably something they chose to skip. But this is just my guess. :)
    – bojan
    Feb 13, 2013 at 15:20
  • Good point with the temporary tables! Feb 13, 2013 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.