None of the Transact-SQL code that we have written so far in this book is reusable. SQL Server allows you to create reusable programming objects. The SQL Server reusable programming objects include views, stored procedures, functions, and triggers (based on either DDL or DML). In this chapter, we first take a look at variables. Next, we take a look at control-flow statements. Then we take a look at the design and use of each of the programmable objects in turn. Finally, we learn how to handle errors that occur in the Transact-SQL batches and programmable objects using a TRY...CATCH
construct.
After completing this chapter, you will be able to:
Like other programming languages, the SQL Server Transact-SQL language also allows temporary storage in the form of variables. Variables are stored in memory and are accessible only from the batch or stored procedure, or the function in which they are declared. There are three types of variables you can create in SQL Server: local variables (based on system or user-defined data types), cursor variables (to store a server-side cursor), and table variables (that is, structured like a user-defined table).
We can declare a variable as a standard variable in Transact-SQL by prefixing it with the @
symbol. We use the DECLARE
statement to declare a variable or multiple variables.
The basic syntax for creating a local variable is as follows:
DECLARE @variable_name [AS] data_type
By default, all local variables are initialized as NULL
. We can assign a value to a local variable in one of the following three ways:
SET
keyword, which is the preferred methodSELECT
statementFor example, the following T-SQL code shows the assignment of values to local variables using these methods:
SET
keyword:--Example of assigning a value to the local variable using the --SET keyword. DECLARE @var1 [int], @var2 [varchar](10); SET @var1 = 10 SET @var2 = N'MyValue1';
SELECT
statement:--Example of assigning a value to the local variable using the --SELECT statement. DECLARE @var3 [int], @var4 [varchar](10); SELECT @var3 = 20 , @var4 = N'MyValue2';
--Example of assigning a value to the local variable at --declaration. DECLARE @var5 [int] = 30, @var6 [varchar](10) = N'MyValue3'; GO
SQL Server supports cursor variables primarily to provide backward compatibility with batches, scripts, and programmable objects written for earlier SQL Server versions.
The syntax to create a cursor variable is as follows:
DECLARE @variable_name CURSOR
The following is an example of creating a cursor variable:
DECLARE @cur_variable1 CURSOR;
Table variables behave in the same manner as local variables. A table variable stores the data in the form of a table. They are suitable for smaller data sets (typically less than 1,000 rows). The basic syntax for creating a table variable is as follows:
DECLARE @table_variable_name [AS] table ( [(column_definition) [,...n])] )
The following is a basic example of creating a table variable:
DECLARE @Table1 TABLE ( COL1 [int], COL2 [varchar](30) );
As you can see in the previous example, the columns of table variables are defined in the same way as you define columns when creating an actual table. Table variables do not support FOREIGN KEY
constraints. Moreover, prior to SQL Server 2014, the only way to create indexes on table variables was by defining the PRIMARY KEY
or UNIQUE KEY
constraint on the table variable columns. However, SQL Server 2014 Database Engine supports non-unique clustered and non-clustered indexes for table variables. We can define indexes on table variables using new inline index specification syntax. The following is an example of inline index creation on a table variable:
DECLARE @Table2 TABLE ( COL1 [int], COL2 [varchar](30), COL3 [datetime], INDEX [ixc_col3] CLUSTERED (col3) WITH (FILLFACTOR=80), INDEX [ixnc_col1_col2] NONCLUSTERED (col1, col2) WITH (FILLFACTOR=80) ); GO
18.117.230.81