How table-valued parameters are used in SQL Server 2008 databases

In a database in SQL Server 2005 or earlier, a table variable is not a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either send a list of records each time, or come up with other workarounds to meet the requirements. Although a SqlBulkCopy object is provided in. NET 2.0 to deliver multiple data rows to SQL Server at once, multiple rows of data cannot be passed to the stored procedure at once.

The T-SQL feature in SQL Server 2008 adds table-valued parameters. With this new feature, we can easily pass a table as a parameter to a stored procedure through a T-SQL statement, or through an application.

1, user-defined table types

When you first look at the new table-valued parameters, I think it's a bit complicated to use this feature. There are several steps. The first thing to do is define the phenotype. In the "programmability" → "type" node in Management Studio 2008, you can see "user-defined table Types (User-defined table type)" as shown in Figure 1.

Figure 1: User-defined table types

Right click on the pop-up menu to select "New user-defined table ..." , the query window in a new template is created, as shown in Figure 2.

Figure 2: User-defined table type creation statement

Click the "Specify Values for Template Parameters (Specify a value as template parameter)" button to explore a dialog box, as shown in Figure 3.

Figure 3: Specifying the value of the template parameter column

After filling in the appropriate values, click the OK button and a "CREATE TYPE" declaration replaces the template. At this point, you can also manually add some columns, or add some restrictions, and finally click the OK button.

The following is the final code:

-- ================================
-- Create User-defined Table Type
-- ================================
USE Test
GO
-- Create the data type
CREATE TYPE dbo.MyType AS TABLE
(
col1 int NOT NULL,
col2 varchar(20) NULL,
col3 datetime NULL,
　　PRIMARY KEY (col1)
)
GO

After you run the code, the definition of the object is set up, and you can view the properties in the User-defined table type (user custom table types), as shown in Figure 4, but you cannot modify them. If you want to modify the type, you can only delete it, and then create it again according to the modified property.

Figure 4: Viewing the properties of a user-defined table type