Below is the presentation of the Transact-SQL statements used to process scripts. These statements, which are sometimes referred to as T-SQL statements, are specific to SQL Server. You’ll learn how to code many of these statements throughout this chapter.
Two statements I want to present right now are USE and PRINT. You can see both of these statements in the script presented below. You use the USE statement to change the current database within a script. In this example, the USE statement makes the AP database the current database. That way, you don’t have to worry about setting the current database using the drop-down list in the Management Studio. And when you create stored procedures, functions, and triggers as you’ll learn in the next chapter, you have to use the USE statement.
You use the PRINT statement to return a message to the client. If the client is the Management Studio, for example, the message is displayed in the Mes-sages tab of the Query Editor. The script in this section includes two PRINT statements. Notice that the first statement uses concatenation to combine a literal string with the value of a variable. You’ll learn how to work with variables as well as the other statements in this script in a moment.
Two statements I won’t present in this chapter are GOTO and RETURN. I recommend that you don’t use the GOTO statement because it can make your scripts difficult to follow. And the RETURN statement is used most often with stored procedures, so I’ll present it in the next chapter.
Transact-SQL statements for controlling the flow of execution
| Keyword | Description |
|---|---|
| IF … ELSE | Controls the flow of execution based on a condition |
| BEGIN… END | Defines a statement block |
| WHILE | Repeats statements while a specific condition is true |
| BREAK | Exits the innermost WHILE loop |
| CONTINUE | Returns to the beginning of a WHILE loop |
| TRY…CATCH | Controls the flow of execution when an error occurs |
| GOTO | Unconditionally changes the flow of execution |
| RETURN | Exits unconditionally. |
Other Transact-SOL statements for script processing
| Keyword | Description |
|---|---|
| USE | Changes the database context to the specified database |
| Returns a message to the client | |
| DECLARE | Declares a local variable |
| SET | Sets the value of a local variable or a session variable |
| EXEC | Executes a dynamic SQL statement or stored procedure |
The syntax of the USE statement
1 | USE database |
The syntax of the PRINT statement
1 | PRINT string expression |
A script that uses some of the statements shown above
1 2 3 4 5 6 7 8 | USE AP; DECLARE @TotalDue money; SET @TotalDue = (SELECT SUM(InvoiceTotal - PaymentTotal - CreditTotal) FROM Invoices); IF @TOtalDue > 0 PRINT 'Total invoices due = $' + CONVERT(varchar,@TotalDue,1); ELSE PRINT 'Invoices paid in full'; |
Description
- These statements are used within SQL scripts to add functionality similar to that provided by procedural programming languages.
- These statements are part of the Transact-SQL, or T-SQL, language and aren’t available on SQL-based systems other than SQL Server.
