Click here to Skip to main content
Click here to Skip to main content

Pass Serialized Tabular Data as a Stored Proc Parameter

, 29 May 2007
Rate this:
Please Sign up or sign in to vote.
How to solve the age-old problem of passing complex structures as parameters to sprocs

Introduction

This article attempts to solve the age-old issue of passing complex tabular data structures into sprocs as parameters. I have seen numerous implementations ranging from simple and elegant to complex and verbose. While this solution may have its flaws, it is simple, quick and very extensible!

No while loops or casting data types...

What I wanted was, ideally, the ability to pass a serialized result set into a stored proc as a parameter, without having to do lots of nested loops, string parsing and casting of varchars to other data types. The result was so simple that I couldn't believe I hadn't thought of it sooner.

Using the code

Below is an example of how it works. You can extrapolate the same concept for multiple parameters instead of just one, but here goes...

One example is worth a thousand words, so I used the common scenario of Order and OrderItems:

create proc addOrder @customerid int, @paymentauthid varchar(50), 
    @orderitems varchar(8000), @deliveryid int
as 
begin tran
    --create orderitem hash table
    create table #orderitem(prodid int not null, 
        qty int not null, price money)
    declare @sql varchar(8000), @orderId int
    --construct insert for all orderitem rows
    set @sql = 'insert #orderitem(prodid, qty, price) select ' + 
        replace(@orderitems, ';', ' union select ')
    execute(@sql)

    insert orders(customerid, orderdate, orderstatus, paymentauthid, 
        deliveryid)
    values(@customerid, getdate(),1, @paymentauthid, @deliveryid )
    select @orderid =  @@identity from orders
    insert orderitem(orderid, prodid, qty, price)
    select @orderid, prodid, qty, price from #orderitem

    if @@error <> 0 begin
        rollback tran
    end 
    commit tran

First, create a hash table representing the tabular data structure that you would like to pass in as a parameter, in this case for @orderitems.

create table #orderitem(prodid int not null, qty int not null, price money)

Instead of looping and casting substrings of the "@orderitems" variable, simply construct this simple insert statement and replace your row delimiter ';' with ' union select '.

set @sql = 'insert #orderitem(prodid, qty, price) select ' + 
    replace(@orderitems, ';', ' union select ')

Finally, execute the constructed SQL statement to populate your hash table, which you can then use for whatever purpose you like.

execute(@sql)

Calling the proc is now as simple as...

addOrder 1, 'ABC123', '1,2, 22.50; 2,4, 110; 3,6, 250.30; 
    4,8, 32.25; 5,10, 20; 6,12, 1000.99; 7,14, 211.45', 2

Points of interest

This is not the first solution to this problem and it certainly won't be the last, but for me it is really simple and, above all, fast to implement.

History

  • 29 May, 2007 - Original version posted

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

habanero
Web Developer
South Africa South Africa
Software Engineer. Programming since 1992.
C#, C/C++, VB/VB.NET, SQL, PowerBuilder, HTML, XML, Javascript, ActionScript, Flex, JSON, ASP/ASP.NET, PHP, Delphi.
 
I specialise in Enterprise Web2 Applications.

Comments and Discussions

 
GeneralThanks Pinmemberc_paresh5-Jun-07 15:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150302.1 | Last Updated 29 May 2007
Article Copyright 2007 by habanero
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid