So cool, still can’t get over how much this rulz!

Back a few years ago, I was working on a large project with a group of developers, and one in particular – Craig Boland, a super-senior .Net/business logic layer programmer based in Dallas – comes up and says, “Hey man, I need to call this proc 1000 times in a row. Is there a way that I can call it only once and process all of these records?”  I says, “No man, they haven’t invented table-valued parameters yet in SQL Server!” Craig walked away, head down and thoroughly disgusted with me. From that point, I thought to myself, “I’ve got to find this dude some way to do this.”  But, before I could create some hack, they came up…table-valued parameters.  

Table-valued parameters are awesome! Below is a minimal example (included are both the TSQL and c#) for the demo. Think I saw something like this in a Dell flyer, but can’t place the source. Doesn’t matter. Anyway, I pasted my TSQL first because it seems to make more sense. Essentially, what it does is take a data set, add it as a structured type, and then processes the entire record set. No need for multiple round trips, and no need for all of the overhead of firing over and over a SQL Server stored procedure.  They also (BOL):


  • Do not acquire locks for the initial population of data from a client.
  • Provide a simple programming model.
  • Enable you to include complex business logic in a single routine.
  • Reduce round trips to the server.
  • Can have a table structure of different cardinality.
  • Are strongly typed.
  • Enable the client to specify sort order and unique keys.



    I really think this is one of the coolest features in SQL Server 2008. And yeah it’s probably all over the internet, so consider this all over +1.

    Enjoy!
    Lee

     
    -- Create table to hold items
    DROP TABLE OrderDetail
    GO
    CREATE TABLE OrderDetail
        (OrderId int
        ,ItemId int
        ,Quantity int
        ,SalesPersonId int
        );
    GO
    DROP TYPE OrderLineItems
    GO
    -- Create table type. Acts like a struct, must be READ ONLY when instantiated
    CREATE TYPE OrderLineItems as TABLE 
        (OrderId int
        ,ItemId int
        ,Quantity int
        ,SalesPersonId int
        );
    GO
    DROP PROCEDURE __processItems go CREATE PROCEDURE __ProcessItems @TVP OrderLineItems READONLY AS SET NOCOUNT ON INSERT INTO OrderDetail SELECT OrderId ,ItemId ,Quantity ,SalesPersonId FROM @TVP; GO SELECT * FROM OrderDetail; GO
     
    So, in a nutshell, what we are doing here is creating the table type that will match the DDL of the destination table, and adding that type as an input parameter to the stored procedure that we will call. Simple! For the .Net code, don’t forget to change the server name when you fire this up in your environment.

    using
    System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Data.Sql; namespace TVP { class TVPdemo { public static void Main(string[] args) { DataTable dt = new DataTable("OrderItems"); dt.Columns.Add("OrderId", typeof(int)); dt.Columns.Add("ItemId", typeof(int)); dt.Columns.Add("Quantity", typeof(int)); dt.Columns.Add("SalesPersonId", typeof(int)); dt.Rows.Add(1, "100", 2, 1000); dt.Rows.Add(1, "101", 15, 1001); dt.Rows.Add(1, "102", 20, 1002); SqlConnection conn = new SqlConnection("data source = S03777\\SQLEXPRESS2K8;" + " Initial Catalog = TEST; Integrated Security = SSPI;"); conn.Open(); SqlCommand cmd = new SqlCommand("__ProcessItems", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter parm = new SqlParameter("TVP", SqlDbType.Structured); parm.Value = dt; cmd.Parameters.Add(parm); cmd.ExecuteNonQuery(); conn.Close(); } } }


    -----------------------------------

    Snaps to the SQL team for this one!!! Niiiiiiiiiiiiice!


     


  • del.icio.us Tags:
    Technorati Tags:

    Posted in:   Tags:
    blog comments powered by Disqus

    by Lee Everest, M.S.

    Poll

    Do you use Azure or cloud in your organization?



    Show Results

    Ads

    Search


    Month List

    Calendar

    «  February 2012  »
    MoTuWeThFrSaSu
    303112345
    6789101112
    13141516171819
    20212223242526
    2728291234
    567891011
    View posts in large calendar

    Tags

    Disclaimer
    The opinions, code, examples, et.al. expressed herein are my own personal opinions and do not represent my employer's view in any way, shape form, or fashion.  All code for demonstration purposes - no guarantees, either written or implied, are made.

    © Copyright 2012 Lee Everest's SQL Server, etc. weblog