Transact-SQL Fundamentals

T-SQL is designed to add structure to the handling of sets of data. Because of this, it does not provide several language features that application development needs. If you do a lot of application programming development, you’ll find that T-SQL is in many ways the exact opposite of how you think when programming in VB, C#, Java, or any other structured development language.

T-SQL Batches
A query is a single SQL DML statement, and a batch is a collection of one or more T-SQL statements. The entire collection is sent to SQL Server from the front-end application as a single unit of code. SQL Server parses the entire batch as a unit. Any syntax error will cause the entire batch to fail, meaning that none of the batch will be executed. However, the parsing does not check any object names or schemas because a schema may change by the time the statement is executed.

Terminating a Batch
A SQL script file or a Query Analyzer window may contain multiple batches. If this is the case, a batch-separator keyword terminates each batch. By default, the batch-separator keyword is go (similar to how the Start button is used to shut down Windows). The batch-separator keyword must be the only keyword in the line. Any other characters, even a comment, on the same line will neutralize the batch separator.
The batch separator is actually a function of SQL Server Management Studio, not SQL Server.  It can be modified in the Query Execution page by selecting Tools➪Options, but I wouldn’t recommend creating a custom batch separator (at least not for your friends). Terminating a batch will kill all local variables, temporary tables, and cursors created by that batch.

DDL Commands
Some T-SQL DDL commands, such as Create Procedure, are required to be the first command in the batch. Very long scripts that create several objects often include numerous go batch terminators. Because SQL Server evaluates syntax by the batch, using go throughout a long script also helps locate syntax errors.

Switching Databases
Interactively, the current database is indicated in the SQL Editor toolbar and can be changed there. In code, the current database is selected with the use command. Use can be inserted within a batch to specify the database from that point on:
USE CHA2
It’s a good practice to explicitly specify the correct database with the use command, rather than assume that the user will select the correct database prior to running the script.

Executing Batches
A batch can be executed in several ways:
✦ A complete SQL script (including all the batches in the script) may be executed by opening the .sql file with SQL Server Management Studio’s SQL Editor and pressing F5, clicking the ! Execute toolbar button, or selecting Query -> Execute. I have altered my Windows file settings so that double-clicking a .SQL file opens Query Analyzer.
✦ Selected T-SQL statements may be executed within SQL Server Management Studio’s SQL Editor by means of highlighting those commands and pressing F5, clicking the ! Execute toolbar button, or selecting Query -> Execute.
✦ An application can submit a T-SQL batch using ADO or ODBC for execution.
✦ A SQL script may be executed by means of running the SQLCmd command-line utility and passing the SQL script file as a parameter.
✦ The SQLCmd utility has several parameters and may be configured to meet nearly any command-line need.

Executing a Stored Procedure
When calling a stored procedure within a SQL batch, the exec command executes the stored procedure with a few special rules. In a sense, because line returns are meaningless to SQL Server, the exec command is serving to terminate the previous T-SQL command. If the stored-procedure call is the first line of a batch (and if it’s the only line, then it’s also the first line), the stored-procedure call doesn’t require the exec command. However, including the exec command anyway won’t cause any problems and prevents an error if the code is cut and pasted later.
The following two system-stored–procedure calls demonstrate the use of the exec command within a batch:
sp_help;
EXEC sp_help;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s