Geeks With Blogs

Bunch's Blog One day I'll have a catchy subtitle, one day

In SQL Server 2008 you can use table valued parameters which can be pretty useful. In the example I use a very simple one to overcome SQL Server’s lack of having a parameter as an array. The example goes through creating the new type, using it in a stored procedure and calling it from an application (VB.Net in this example). You start off with creating the new type. Under Programmability/Types/User-Defined Table Types create a new table. Here I created a table with a single column to hold an integer.

-- ================================
-- Create User-defined Table Type
-- ================================
USE TestDB

GO

-- Create the data type
CREATE TYPE dbo.IntTable AS TABLE
(
    col1 int NOT NULL,
    PRIMARY KEY (col1)
)
GO

Now that the type is created you need some SQL to use it. In this stored procedure the parameter @PlayerIDList uses the new InTable data type to pass in a list of player IDs. Using a CTE you can go through each player ID in the table and then return basic player information like batting stance, throwing hand, number, etc.

USE TestDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spTestSprocTVP]
    -- Add the parameters for the stored procedure here
    @PlayerIDList IntTable READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    WITH PlayerIDs (PlayerID) AS
    ( SELECT * FROM @PlayerIDList )

    SELECT ply.FullName, ply.Bats, ply.Throws, ply.JerseyNumber
    FROM tblPlayers ply
    INNER JOIN PlayerIDs ON ply.PlayerID = PlayerIDs.PlayerID

To test a sproc like this using a TVP in SQL you can write something like:

DECLARE @test IntTable
INSERT INTO @test(col1) values(282), (643088), (643090), (179100), (54541)
EXEC spTestSprocTVP @test

Where you create a variable of the new data type, load it up with some test values and execute the stored procedure. Now that everything is setup and tested on the SQL side you need to add the code to call it from the application. This example assumes you already have a variable with player IDs created as an ArrayList. The code is also written out (no SQLDataSource used) so you can see all the steps.

Public Function GetPlayerDetails(ByVal playerIDs As ArrayList) As DataTable
    Dim dtPlayerDetails As DataTable = New DataTable
    Dim idList As List(Of SqlDataRecord) = New List(Of SqlDataRecord)
    Dim tvp As SqlMetaData() = {New SqlMetaData("n", SqlDbType.Int)}

    For Each id As Integer In playerIDs
        Dim rec As SqlDataRecord = New SqlDataRecord(tvp)
        rec.SetInt32(0, id)
        idList.Add(rec)
    Next

    Dim cn As SqlConnection = New SqlConnection
    cn.ConnectionString = connString
    Dim cmd As SqlCommand = cn.CreateCommand
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "spTestSprocTVP"
    cmd.Parameters.Add("@PlayerIDList", SqlDbType.Structured)
    cmd.Parameters("@PlayerIDList").TypeName = "IntTable"
    cmd.Parameters("@PlayerIDList").Value = idList

    cn.Open()
    da.Fill(dtPlayerDetails)
    cn.Close()

    Return dtPlayerDetails
End Function

The main points to look at in the function is creating a new list of SqlDataRecord, creating a variable for the SqlMetaData type that matches your user-defined table type from the first step and then adding each item from the function’s parameter to the SqlDataRecord type list. All that is within the first eight lines of the function. Once the DataTable is returned you can do whatever you want with it like bind it to a GridView.

Technorati Tags: ,
Posted on Thursday, November 3, 2011 8:05 AM | Back to top


Comments on this post: Using Table Valued Parameters

# re: Using Table Valued Parameters
Requesting Gravatar...
Hola como seria desde vb6.0 enviando los parametros al store procedure? .. podrias poner un ejemplo por favor y enviarmelo :) ? muchas gracias
Left by Javier on Dec 08, 2012 9:22 AM

# re: Using Table Valued Parameters
Requesting Gravatar...
Para que no se puede desde VB6, solo con XML.
Left by Flavio on Feb 21, 2014 3:48 PM

Your comment:
 (will show your gravatar)


Copyright © Bunch | Powered by: GeeksWithBlogs.net