Chapter 5. Understanding Advanced Database Programming Objects and Error Handling

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:

  • Create and use variables
  • Add logic around and within the Transact-SQL statements to control program execution
  • Design and create user-defined views
  • Design and create user-defined stored procedures
  • Design and create user-defined functions
  • Design and create triggers
  • Handle errors that occur within the Transact-SQL statements and programming objects

Creating and using variables

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.

Creating a local variable

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:

  • By using the SET keyword, which is the preferred method
  • By using the SELECT statement
  • During the declaration of the variable

For example, the following T-SQL code shows the assignment of values to local variables using these methods:

  • By using the 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';
  • By using the 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';
  • During the declaration of the variable:
    --Example of assigning a value to the local variable at 
--declaration.
DECLARE @var5 [int] = 30,
        @var6 [varchar](10) = N'MyValue3';
GO

Creating the cursor variable

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;

Creating the table variable

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
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.218.224.226