Most of the scripts you’ve created so far in this tutorial have consisted of a single SQL statement. However, a script can include any number of statements, and those statements can be divided into one or more batches. To indicate the end of a batch, you code a GO command. The script shown below, for […]
Read More...The Transact-SQL Statements for Script Processing
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 […]
Read More...How to work with Scalar Variables
Statements below present the DECLARE and SET statements that you use to work with variables. Specifically you use these statements to work with scalar variables, which can contain a single value. You use the DECLARE statement to create a variable and specify the type of data it can contain, and you use the SET statement […]
Read More...How to work with Table Variables
Script shown below presents the syntax of the DECLARE statement you use to create table variables. A table variable is a variable that can store the contents of an entire table. To create this type of variable, you specify the table data type in the DECLARE statement rather than one of the standard SQL data […]
Read More...How to Work with Temporary Tables
In addition to table variables, you can use temporary tables to store table data within a script. Temporary tables are useful for storing table data within a complex script. In addition, they provide a way for you to test queries against temporary data rather than permanent data. Unlike a table variable, a temporary table exists […]
Read More...A comparison of the Five Types of Transact-SQL Table Objects
Now that you’ve learned about table variables and temporary tables, you might want to consider when you’d use them within a script and when you’d create a new standard table or view or simply use a derived table instead. Table below presents a comparison of these five types of table objects. Note that although a […]
Read More...How to Perform Conditional Processing
The ability to control the execution of a program is an essential feature of any procedural programming language. T-SQL provides three basic control structures that you can use within scripts. You can use the first one to perform conditional processing, you can use the second one to perform repetitive processing, and you can use the […]
Read More...How to Test for the Existence of a Database Object
Frequently, you’ll need to write scripts that create and work with database objects. If you try to create an object that already exists, SQL Server will return an error. Similarly, SQL Server will return an error if you try to work with an object that doesn’t exist. To avoid these types of errors, you should […]
Read More...How to Perform Repetitive Processing
In some cases, you’ll need to repeat a statement or a block of statements while a condition is true. To do that, you use the WHILE statement that’s presented below. This coding technique is referred to as a loop. The script in this section illustrates how the WHILE statement works. Here, a WHILE loop is […]
Read More...How to Handle Errors – Coding Scripts
SQL Server 2005 introduced a TRY … CATCH statement that makes it much easier to handle errors than it was in previous versions of SQL Server. Handling errors is often referred to as error handling or exception handling, and the TRY … CATCH statement works similarly to the exception handling statements that are available from […]
Read More...