Pass Serialized Tabular Data as a Stored Proc Parameter

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