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:,