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

?
1
2
3
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
'-------------------------------------
' 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()