Working with Table type

Introduction

A new type appeared in SQL server 2008. This type is Table.
You can now pass a table as parameter to a store procedure.

As sample we are going to send the selected values (multiple selection) of a listview to a store procedure in order to insert the data in a parent table and a child table.

Creating a new table user type
First of all we have to create a new table type

CREATE TYPE [dbo].[Tble_KeywordsID] AS TABLE(
[ID_Keywords] [int] NOT NULL
)


As you can see this type creation follow the same principle than a regular table.
Once created, you can visualize this table in databasename\programmability\types\User-Defined Table Types

It really looks like a table against witch you can define keys, constraints and indexes.
That means, you are not only passing data like in an array, but in a real table than can be optimized and indexed if necessary.

Below, I wrote a sample (asp.net / vb) where you can understand how to use pragmatically in a project this functionnality.

Table creation

CREATE TABLE [dbo].[Tble_Issues_Keywords](
[Id_Issues_Keywords] [int] IDENTITY(1,1) NOT NULL,
[Id_Issue] [int] NULL,
[Id_Keyword] [int] NULL,
CONSTRAINT [PK_Tble_Issues_Keywords] PRIMARY KEY CLUSTERED
(
[Id_Issues_Keywords] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Tble_Issues_Keywords] WITH CHECK ADD CONSTRAINT [FK_Tble_Issues_Keywords_Tble_Issues] FOREIGN KEY([Id_Issue])
REFERENCES [dbo].[Tble_Issues] ([Id_Issue])
GO

ALTER TABLE [dbo].[Tble_Issues_Keywords] CHECK CONSTRAINT [FK_Tble_Issues_Keywords_Tble_Issues]
GO

CREATE TABLE [dbo].[Tble_Issues](
[Id_Issue] [int] IDENTITY(1,1) NOT NULL,
[Id_User] [int] NULL,
[Id_Folder] [int] NULL,
[Issue_Content] [char](50) NULL,
[Issue_Description] [varchar](max) NULL,
[Issue_Solution] [varchar](max) NULL,
[Creation_Date] [datetime] NULL,
[Update_Date] [datetime] NULL,
CONSTRAINT [PK_Tble_Sujets] PRIMARY KEY CLUSTERED
(
[Id_Issue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored procedure creation

CREATE PROCEDURE [dbo].[sp_Insert_Issues_Keywords_Folders]

@Id_User INT
,@Id_Folder INT
,@Issue_Content CHAR(50)
,@Issue_Description VARCHAR(MAX)
,@Issue_Solution VARCHAR(MAX)
,@Tble_Id_Folders AS Tble_KeywordsID READONLY

AS

BEGIN TRANSACTION

DECLARE @Id_Issue INT

INSERT INTO dbo.Tble_Issues
(
Id_User
,Id_Folder
,Issue_Content
,Issue_Description
,Issue_Solution
,Creation_Date
)
VALUES
(
@Id_User
,@Id_Folder
,@Issue_Content
,@Issue_Description
,@Issue_Solution
,GETDATE()
)

SELECT @Id_Issue = @@IDENTITY

INSERT INTO dbo.Tble_Issues_Keywords
(
Id_Issue
,Id_Keyword
)
SELECT
@Id_Issue
,ID_Keywords
FROM @Tble_Id_Folders

COMMIT TRANSACTION

Code sample (VB.net)

The following code is based on a form that includes :

  • A list named lstKeywords
  • A stored procedure named sp_Search_Issues
  • A connection named ConnectionInfo and stored in a separate XML file (using SSPI specification)
'-------------------------------------
' Variables declaration
'-------------------------------------
Imports System.Data.SqlClient
Public myConnection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionInfo"))
Private _dataTable As DataTable
Dim dt_KeywordsID As DataTable = New DataTable("Tble_KeywordsID")
Dim dtc_KeywordsID As DataColumn
Dim row As DataRow

Dim objCommand As SqlCommand
Dim objParam As SqlParameter

'-------------------------------------
' Create a connection to SQL Server
'-------------------------------------
objCommand = New SqlCommand("sp_Search_Issues", myConnection)
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection.Open()

'-------------------------------------
' Create parameters for sp
'-------------------------------------
objParam = objCommand.Parameters.Add("@Id_Folder", SqlDbType.Int)
objParam.Value = CInt(lstFolders.SelectedValue)

objParam = objCommand.Parameters.Add("@Tble_Id_Folders", SqlDbType.Structured)

'-------------------------------------
' Prepare table as parameter
'-------------------------------------
dtc_KeywordsID = New DataColumn()
dtc_KeywordsID.DataType = Type.GetType("System.Int32")
dtc_KeywordsID.ColumnName = "ID_Keywords"
dt_KeywordsID.Columns.Add(dtc_KeywordsID)

'-------------------------------------
' Get the multiple selected values
'-------------------------------------
Dim intLoop As Integer
Dim valSelect As Integer
Dim colSelectedValues As New Collection

For intLoop = 0 To lstKeywords.Items.Count - 1
If lstKeywords.Items(intLoop).Selected Then
valSelect = lstKeywords.Items(intLoop).Value
row = dt_KeywordsID.NewRow()
row("ID_Keywords") = valSelect
dt_KeywordsID.Rows.Add(row)
End If
Next

'-------------------------------------
' Value affectation
'-------------------------------------
objParam.Value = dt_KeywordsID

'-------------------------------------
' Define a datareader and store the
' resultset of the sp's execution
' within this datareader
'-------------------------------------
Dim MyDataReader As SqlDataReader
MyDataReader = objCommand.ExecuteReader()

'-------------------------------------
' Define data value field and a
' data text field , source
' and bind the datareader to the list
' resultset of the sp's execution
' within this datareader
' Display the results in a listview
'-------------------------------------
lstViewIssuesResults.DataValueField = "Id_Issue"
lstViewIssuesResults.DataTextField = "Issue_Content"
lstViewIssuesResults.DataSource = MyDataReader
lstViewIssuesResults.DataBind()

'-------------------------------------
' Close the connexion object
'-------------------------------------
objCommand.Connection.Close()