StrataFrame Forum » Updates and Information » StrataFrame Users Contributed Samples » Table Valued Parameters - Passing a table into a sproc in SQL 2008

Table Valued Parameters - Passing a table into a sproc in SQL 2008

Charles R Hankey — Fri, 12 Nov 2010 08:14:18 GMT

Core concept :
You can now pass full tables - in fact anything that implements iEnumerable - into a SQL Server 2008 SProc
THIS IS BIG !

Walkthrough :

First, you need to create a user-defined table type. This establishes the schema of the data you will pass in. The datatable you pass in to the parameter that is defined as being of this type must match this schema as well.

In your database, go to the node database/programmability/types/user-defined types and right click for CREATE script

[codesnippet]USE [membership]
GO
/****** Object: UserDefinedTableType [dbo].[TVPGuidkeys] Script Date: 09/24/2009 20:10:11 ******/
CREATE TYPE [dbo].[TVPGuidkeys] AS TABLE(
[guidkey] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[guidkey] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO[/codesnippet]

Here I am creating the schema for a one column table where guidkey will contain a UID key ( system.guid on the .net side) which will be pks in the Members table

Now a sproc that will get Members matching the pks that are passed in :

[codesnippet]

USE [membership]

/****** Object: StoredProcedure [dbo].[get_Members_by_pklist] Script Date: 09/24/2009 20:23:39 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Charles Hankey

-- Create date: 09-24-09

-- Description: Pull records from Members based on passed in table of pks

-- =============================================

CREATE PROCEDURE [dbo].[get_Members_by_pklist]

-- Add the parameters for the stored procedure here

@pklist dbo.TVPGuidkeys READONLY

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT Members.cPK_Members,

Members.cCompany,

Members.cFirst_Name,

Members.cLast_Name,

Members.cClassCode,

mc.cClass

FROM dbo.Members

JOIN dbo.MembershipClasses mc

ON dbo.Members.cClassCode = mc.cClassCode

WHERE dbo.Members.cPK_Members IN

(SELECT guidkey FROM @pklist)

END

[/codesnippet]

Notice : @pklist dbo.TVPGuidkeys READONLY

@pklist can be treated like a table - you could do a left join to it and achieve the same thing here as the sub-query. You could also use it to do an insert if you had structured the param to pass in a set of records !!

You cannot change the values of the @pklist

Now here is the .NET side in your SF app :

this is in the MembersBO. I will later create an overload that receives a pklist as List(Of System.integer) That will require another TVPIntKeys in the database if I want to create sprocs to receive integer PKs.

[codesnippet]    Public Sub get_Members_by_pklist(byval pklist As List(Of System.Guid))

        Dim pktbl As DataTable = New DataTable("pktbl")
        Dim newrow As DataRow

        Dim colname As DataColumn = New DataColumn("guidkey")

colname.DataType = System.Type.GetType("System.Guid")

pktbl.Columns.Add(colname)

        For Each pk As System.guid In pklist
            newrow = pktbl.NewRow()
            newrow("guidkey")=pk
            pktbl.Rows.Add(newrow)
        Next

Dim cmd As SqlCommand = new SqlCommand("dbo.get_members_by_pklist")

cmd.CommandType = commandtype.StoredProcedure

        Dim oparm As SqlParameter = cmd.Parameters.AddWithValue("@pklist",pktbl)
        oparm.SqlDbType = sqldbtype.Structured
        oparm.TypeName = "dbo.TVPGuidkeys"

Me.FillDataTable(cmd)

End Sub[/codesnippet]

Under the category of weird things I didn't expect : the ADO stuff - colname.DataType = System.Type.GetType("System.Guid") appears to be case-sensitive. :w00t: At least I was getting design-time errows when I used system.type.getype ...

Now I call the fill method when I come back from a browse dialog (the BD was driven by a SQL view with very denormalized data )

[codesnippet] Private Sub bdMembers_BrowseDialogClosed(ByVal e As MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogClosedEventArgs) _
Handles bdMembers.BrowseDialogClosed

If Me.Members_lookupBO1.Count > 0 Then

Dim pks As New System.Collections.Generic.List(Of System.Guid)

'find all the pks of the records selected in the browsedialog

            For Each bo As Members_lookupbo In Me.Members_lookupBO1.GetEnumerable()
                pks.Add(bo.cPK_Members)
            Next

'get all the records for the selected pks using a stored procedure

Me.MembersBO1.get_Members_by_pklist(pks)

'this is a listview on the maintenance form

            Me.lvmembers.Requery()

        End If
    End Sub[/codesnippet]

There it is. FillbyPrimaryKey using a sproc. And the tools to use datatables in all sorts of ways in conjunction with sprocs.

See the thread on this topic in Business Objects for some links to Kevin Goff's Code Magazine articles.

RE: Table Valued Parameters - Passing a table into a sproc in SQL 2008

Charles R Hankey — Fri, 12 Nov 2010 08:14:18 GMT

Boy am I glad I wrote this up the first time around! Now I actually really need it and frankly I'm having to read it over multiple times to remember what the heck I did :)

In using sprocs for Xtrareports datasets, I'm finding there will be a lot of times when I need to first get the records for the main band, and then get child records for all the records in the first set, In order to do this with 2 sprocs - one for parent and then one for children connected to the selected parent set - I need a way to tell the child sproc which record to pull based on matching fks with a list of parent pks.

And this is the way to go, unless somebody else has a suggestion ...

RE: Table Valued Parameters - Passing a table into a sproc in SQL 2008

Charles R Hankey — Fri, 25 Sep 2009 20:31:30 GMT

I just took my first leap into Generic methods so don't laugh - I think this is generic. The goal is to have one sub in my basebo that can handle collections of pks whatever the data type. In my previous post in this topic i have a method that works specifically for guid keys. Here is that method refactored

[codesnippet] Public Sub FillfromSproc_by_pklist(Of T) (byval pklist As List(Of T),Byval sproc As string)

' I need the name of the TVP defined in that database. That is a schema that needs to know the name of the
' the pkkey column - renamed from previous guidkey/intkey version
' still have 2 TVPs - could probably get around that by using a single TVP with multiple columns for different
' data types and only using the one appropriate, but for right now it is just as easy to do it this way

' build the name of the TVP being used to declare the parm @pklist in the sproc

Dim TType As String = gettype(T).Name
Dim typestr As String = "dbo.TVP" & TType & "keys"

' since this is the function that actually builds the datatable it needed to be
' refactored as well, getting its type for the pkkey column - renamed to be type agnostic
' from the type of the pklist that is being passed in - see below

Dim pktbl As DataTable = Utility.PKlisttoDatatable(pklist)
Dim cmd As SqlCommand = new SqlCommand(sproc)

cmd.CommandType = commandtype.StoredProcedure
Dim oparm As SqlParameter = cmd.Parameters.AddWithValue("@pklist",pktbl)
oparm.SqlDbType = sqldbtype.Structured

'use the TVP type name built above for the type represented by T

oparm.TypeName = typestr

Me.FillDataTable(cmd)

End Sub[/codesnippet]

Now the refactor of the function that converts a list(Of T) to a datatable with a single column of type T

[codesnippet] Public Shared Function PKListtoDatatable(Of T)(byval pklist As List(Of T)) As DataTable

Dim pktbl As DataTable = New DataTable("pktbl")
Dim newrow As DataRow

Dim colname As DataColumn = New DataColumn("pkkey")

' this made me feel like "Hey baby, now you're cookin' with Generics !"

colname.DataType = gettype(T)

pktbl.Columns.Add(colname)

For Each pk As T In pklist
newrow = pktbl.NewRow()
newrow("pkkey")=pk
pktbl.Rows.Add(newrow)
Next
Return pktbl

End Function[/codesnippet]

This is now part of my BaseBO

The requirement for the sproc that receives it is that it declares a parm called @pklist of a previously created table type for the correct type of the pklist that will be passed in

Suggestion for further extension or refactor most welcome

RE: Table Valued Parameters - Passing a table into a sproc in SQL 2008

Greg McGuffey — Fri, 25 Sep 2009 14:37:13 GMT

This is awesome Charles! Thanks!

RE: Table Valued Parameters - Passing a table into a sproc in SQL 2008

Edhy Rijo — Thu, 24 Sep 2009 21:40:16 GMT

Hi Charles,

Thanks for the contribution. I will have to print it out to parse the whole concept and digest it :D.

I never used store procedures in my VFP application and suddenly after I learned how to use them in SF I now have 20+ in my current project and loving them. :P

RE: Table Valued Parameters - Passing a table into a sproc in SQL 2008

Charles R Hankey — Thu, 24 Sep 2009 19:58:03 GMT

To clarify when i talked about creating an overload :

Now that i have the

get_Members_by_pklist(byval pklist As List(Of System.Guid))

working I will abstract this to my basebo as FillbyPKlist(byval pklist AS List(Of T), byval sproc as string)

so that I can pass in a list of whatever type is used for pks and call whatever sproc I have setup to use it. The important thing is that there will only be one TVP definition for each datatype (and they have been added to my Model DB) and any sproc I create that is meant to use this collection to pull (or not pull) records will use the appropriate type for its @pklist