Tuesday, 17 February 2015
Wednesday, 11 February 2015
Table-Valued Parameters and Merge Statement in SQL Server 2008
SQL Server 2008 introduces a new parameter type called the table-valued parameters. With the table-valued parameters, which are declared by using user-defined table types as will be described below, you can now send multiple rows of data to a function or stored procedure without creating a temporary table or many parameters.
To illustrate the use of table-valued parameters in comparison to how stored procedures or functions are used prior to SQL Server 2005, let's say you are maintaining a table of contacts ([dbo].[Contact]) that contains the email address, first name and last name of your contacts. You would create a stored procedure that accepts as separate parameters the email address, first name and last name.
What the stored procedure does is insert the new contact information if the email address does not exist in the [dbo].[Contact] table. If the email address already exists, it updates the first name and last name of the contact. To add or update contacts, you would have to call the stored procedure as many times as you have contacts.
The Table-Valued Parameter Way
The stored procedure above can be modified to accept just one parameter, which is the table-valued parameter, instead of 3 different parameters for the email address, first name and last name. The first step in making use of table-valued parameters is to create a user-defined table type. A user-defined table type is a user-defined type that represents the definition of a table structure. To create a user-defined table type, you will use the CREATE TYPE statement, followed by the table structure definition.
The next step after creating the user-defined table type is to use that type as the parameter of the stored procedure. In the case of the stored procedure above, we will be replacing the 3 parameters with just one parameter, the table-valued parameter.
The parameters of the stored procedure now becomes like this:
One thing to note in this parameter is the READONLY attribute. Table-valued parameters must be passed as input READONLY parameters to stored procedures or functions. If you forget to include the READONLY attribute, you will get the following error message:
One thing to remember also is that DML operations such as DELETE, INSERT or UPDATE on a table-valued parameter in the body of a stored procedure or function are not allowed. If you try to issue a DELETE, INSERT or UPDATE statement into a table-valued parameter, you will get the following error message:
The body of the stored procedure will also change since we are now processing multiple rows of contacts instead of one contact at a time. The updated stored procedure, which now uses a table-valued parameter, will now look something like the following:
To use this updated stored procedure, we first have to declare a local variable whose type is the user-defined table type we defined earlier and which is the type that the stored procedure is expecting.
Now we fill this table variable with the list of contacts we want to add or update in the [dbo].[Contact] table.
The syntax of the INSERT statement shown here is the new way of inserting multiple values to a table using a single INSERT statement, which is introduced in SQL Server 2008. For more details about this new feature, you can refer to the article entitled Multiple Value Inserts Using a Single INSERT Statement.
The last step is to pass the table variable to the stored procedure:
The complete script in calling the stored procedure that uses the table-valued parameter is as follows:
One of the common tasks in database management is the maintenance of lookup tables. Sample lookup tables are Currency Codes , Country Codes, U.S. State Codes, Product Types and so on. Maintenance of these look-up tables are usually done using stored procedures where the normal task is to add the new record if it does not exist or update the existing record if it already exists based on the identified primary key of the table. This involves 2 statements, issuing either an INSERT statement or an UPDATE statement depending on the existence of the record.
SQL Server 2008 introduces a new statement called MERGE statement which combines these functionalities into just a single statement. The MERGE statement performs an INSERT, UPDATE or DELETE operation on a target table based on the results of a join with source table.
To illustrate how the MERGE statement is used, let's look at the process of maintaining a table using a stored procedure that performs either an INSERT statement if the record does not exist or an UPDATE statement if the record already exists in the target table. Prior to SQL Server 2008, your stored procedure may look like the following:
The MERGE Statement Way
With the new MERGE statement, the stored procedure above will now look as follows:
Here are the results when calling the stored procedure containing the MERGE statement:
Dissecting the MERGE Statement
Now let's look at the syntax of the MERGE statement. The basic syntax of the first part of the MERGE statement is as follows:
This syntax looks totally different from the SELECT, UPDATE, INSERT or DELETE statement that you may have been accustomed to. The first line specifies the target table where the succeeding INSERT, UPDATE or DELETE statement will be performed. The "AS <alias>" part of the syntax is optional. The INTO clause also is optional.
The source table to which to get the records to merge with the target table is specified after the USING clause. The source table can be a table or view, a function that returns a rowset, a derived table, joined tables or even the OPENXML statement.
The join condition "<merge_search_condition>" that joins the target table with the source table is specified after the ON clause. The syntax of the MERGE statement can be compared to a SELECT statement with an INNER JOIN clause where the <target_table> is inner joined with the <table_source> using the <merge_search_condition> specified.
Extracting from the stored procedure above, the first part of the MERGE statement used is as follows:
In this case, the [dbo].[Employee] is the target table while [Target] is the alias associated with this table. As for the source table, instead of using a table or view, a derived table is used using the parameters passed to the stored procedure. The alias [Source] was assigned to this derived table followed by the column alias list corresponding to each column specified in the SELECT statement. Make sure that the number of columns specified in the SELECT statement matches the number of columns in the column alias list. If the number of columns in the SELECT statement is more than the number of columns in the columns list, you will get the following error message:
Similarly, if the number of columns in the SELECT statement is less than the number of columns in the columns list, you will get the following error message:
The second part of the MERGE statement tells what action will be performed for those records that match between the source table and the target table. It also tells what action will be performed for those records that are in the source but are not in the target. In our scenario, what we want to happen is that for those records that exist in both the target table and source table, we would want to update the data in the target table with the values in the source table. On the other hand, for those records in the source table but are not in the target table, we would want to insert these records to the target table. The basic syntax for the second part of the MERGE statement is as follows:
Extracting this part from the stored procedure above:
From this script, you can see that for those records that matched (WHEN MATCHED THEN), the [FirstName], [LastName] and [Position] columns of the target table are updated with the values from the source table. The name of the target table is not specifed after the UPDATE statement because only the target table can be updated by the UPDATE statement. On the other hand, for those records that are in the source table but are not in the target table (WHEN NOT MATCHED THEN), an INSERT statement is executed inserting the records from the source table into the target table for those that don't exist.
Using Row Constructors as Source
In the article Row Constructors (or Table-Valued Constructors) as Derived Table, it discusses the use of row constructors as derived tables and it shows an example of how to use it with the MERGE statement. The stored procedure above can be modified to use row constructors as the source for the MERGE statement. Here's how the stored procedure will look like, with the row constructor highlighted in red.