Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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.
share|improve this answer
1  
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. –  NeverStopLearning Feb 13 '13 at 14:32
1  
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 '13 at 15:20
    
Good point with the temporary tables! –  NeverStopLearning Feb 13 '13 at 16:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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