In this demonstration we are going to look at working with batches. To begin our example we will need to run both the CREATE SCHEMA and CREATE TABLE statements to hold sample data. Then we will use two INSERT statements to add data. Notice we do not have a GO statement on line 7 to divide the CREATE statements from the INSERT statements into separate batches This will produce the following errors.
These errors happen because batches are used to divide code into sections for Parsing Syntax and Resolving Object names. The Msg 156 error is an example of an object resolution error and occurs because SQL has not yet created the SCHEMA or TABLE objects that the INSERT statements needed to add data. In addition DDL statements such as the CREATE SCHEMA statement need to be in their own batches and will generate a syntax error as well. The Msg 102 error is also an example of a parsing syntax error and occurred because the keyword VALUES was misspelled.
We will add the GO statement to line 7 to divide the two code sections into separate batches. Notice we will not correct the misspelled keyword VALUES.
When we run this code we will still get the Parsing Syntax error.
However, our Accounting Schema and BankAccounts Table have both been created. This is because the CREATE statements were in their own batch and was able to run without error. Also, notice that when we run a SELECT statement on the table no records will be displayed.
This is because there was a syntax error in the batch that included both INSERT statements which caused the entire batch to fail. Next, we will fix the misspelling of the keyword VALUES on line 11.
When we run this batch we get the following message. This is because even though the two INSERT statements are in the same batch, they are still two separate implicit transactions.
If we run the SELECT statement again and will see that both INSERT statements ran and added the appropriate records.
One more item of concern with batches is that they not only create a boundary for syntax and parsing, but also they are also boundaries for variables, as well. This example we will declare and initialize a variable and then SELECT the variable in our first batch.
While the first batch ran successfully and displayed the number 5, the second batch will cause the error below to happen. This is because the variable only exists within the batch where it was created.