Pass Arrays To SQL Server

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
 
Have you ever needed to pass a multi-dimensional array as an input parameter to a SQL Server stored procedure?  Of course, SQL Server itself does not support passing arrays to its procedures the same way that a Visual Basic or Visual C++ application would.  So, we need an alternative that will allow us to "duplicate" the same functionality.
If you are familiar with XML, Pass XML Instead of Arrays To SQL Server Procedures will likely be an even better option for you.
 


 
The following is a "scaled down" version of a scenario where just such a need would exist.  In a normal e-commerce type application, we would have a table in SQL Server that would serve to hold our customer data.  For this example, we'll refer to this table as Orders.  We would also need a table to hold the items actually ordered by the customer.  We'll refer to this table as OrderedItems.  Here's the schema:
 
Table Name: Orders
Column Name Data Type
PK_ORDER_ID int
BILL_FNAME varchar(50)
BILL_LNAME varchar(50)
ORDER_DATETME date
   
Table Name: Ordered Items
Column Name Data Type
PK_ORDERED_ITEM_ID int
FK_ORDER_ID int
FK_ITEM_ID int
ORDER_QTY int
One of the challenges we'll face is how to efficiently insert a record into the Orders table and insert record(s) into the OrderedItems table and still be able to rollback the transactions for both if an error should occur.  One solution I discovered (you may discover others as well) is to create a delimited string that could be passed to the stored procedure in an input parameter.  However, as you can see in the schema for OrderedItems, we'll actually need to pass both the item number and the quantity ordered.  So, we'll need more than just a simple delimited value such as a basic comma delimited string.
The approach taken for this example was to define two different delimiters (your application may require more depending on its complexity).  The "|" or pipe character was chosen as an "end of value" delimiter that signals the procedure that we have all of the necessary values for this record stored in the proper variables and are ready to initiate an insert into the OrderedItems table.   The "-" or dash character was chosen as a delimiter to indicate that the next value found should be stored in the variable designated to hold the order quantity.  With this in mind, our delimited string would look like this:
2-12|5-109|3-1|4-2|7-1|1-1|
In the above example, we would have item number "2" with a quantity of "12", item number "5" with a quantity of "109", and item number "3" with a quantity of "1" and so on.  This delimited string would be passed in an input parameter to the stored procedure (we'll use the variable "@item_ordered_array" in the procedure itself).
In a moment, we'll take a look at the code in the stored procedure.  Please keep the following in mind while viewing:
1. This is an extremely scaled down scenario.  You'll most likely have other tables and columns involved in your application.
2. Your application will likely require additional data validation purposely left out of this example so as not to cloud the issue.
3. The coding techniques used here are for increased code readability and ease of understanding.  Your style will likely differ (I know mine does).  
 
Let's take a look at the code:
CREATE PROCEDURE sp_AddOrder 
 (
    @bill_fname                 varchar(50) = Null,
    @bill_lname                	varchar(50) = Null,
    @items_ordered_Array        varchar(500) = '0',
    @new_pk_order_id            int OUTPUT
 )  
AS
 
  DECLARE @returnval int,              
                @nCnt int,
                @nLen int,
                @nGetItem int,
                @nItem int,
                @nQty int,
                @sTmp Varchar(10),
                @sItem VarChar(10),
                @sQty VarChar(10)
    
    BEGIN
    select @returnval = 0

    

    BEGIN TRANSACTION AddOrderTrans
  
   

    insert into Orders(bill_fname,bill_lname,order_datetime) values (@bill_fname,@bill_lname,GetDate()) 

     if (@@ERROR <> 0) Goto OnErrorTrap        
        
    
     select @new_pk_order_id = @@identity 

   /*  get the length of the @items_ordered_Array 
       string for later use.   */
             
     select @nLen = Len(@items_ordered_Array)

  /* set our counter to start with 1 as that will be 
     the first position in the delimited string that 
     we look at   */

     select @nCnt = 1

  /* The variable @nGetItem is used to determine 
     whether the current value should be
     added onto the @sItem variable.  This 
     variable will eventually hold a string/varchar
     version of our Item number.  
     If the @nGetItem = 1, then it is true.   */

     select @nGetItem = 1    

  /* @sItem and @sQty are the string/varchar version
     of the item number and quantity values we will 
     end up inserting into the OrderedItems table.  
     We must set these variables to empty or we'll
     wind up with a Null value in the string(s).   */

     select @sItem = ''
     select @sQty = ''

 /* enter into a loop where we will check each 
    character in the @items_ordered_Array string.  */

     WHILE (@nCnt <= @nLen)
        BEGIN  


           /* Get the current character and store it 
              in the temp variable @sTmp  */

 
           select @sTmp = SUBSTRING(@items_ordered_Array,@nCnt,1)

           
             /* if we find the | or pipe character, then we 
                should attempt an insert into the
                OrderedItems table using the values 
                in @sItem and @sQty variables   */


           if (@sTmp = '|')
              BEGIN

                /* set the next iteration after this one
                   in the loop back to setting the @sItem 
                   variable  */

  
                select @nGetItem = 1      

  
                /*  make sure there are no trailing 
                    blanks in these variables */                

    
                select @sItem = ltrim(rtrim(@sItem))    
                select @sQty = ltrim(rtrim(@sQty))

                
               /* convert these variables to integers as 
                  their data type must match the column data 
                  type during the insert into the OrderedItems
                  table   */


                select @nItem = CAST(@sItem as int)       
                select @nQty = CAST(@sQty as int)          


            /* perform the insert for this item number using
               the newly created @new_pk_order_id value
               from the insert of the Orders table.  */

 
                insert into OrderedItems
                (
                  fk_order_id,
                  fk_item_id,
                  qty
                ) values                                            
                (
                  @new_pk_order_id,
                  @nItem,
                  @nQty
                )

               
                if (@@ERROR <> 0) Goto OnErrorTrap  
               
                /* reset the variables back to an empty
                   string and prepare for another record  */    
               
                    select @sItem = ''
                    select @sQty = '' 
              
              END 

 
          /* check the @sTmp variable and see if we need
             to set the @nGetItem to store future values
             in the @sItem variable.  Remember, we are 
             using the - or dash character to indicate 
             that all future values should be added on 
             to the @sQty variable until we find another 
             delimiter.  */


           if (@sTmp = '-') select @nGetItem = 0 
              

         /* if the current character is not a delimiter 
            character, add the value to the @sItem
            or @sQty variable based on the status of 
            our @nGetItem variable    */
  

         if ((@sTmp <> '|') AND (@sTmp <> '-')) 
              BEGIN
                 if (@nGetItem = '1')  
                    BEGIN
                     select @sItem = ltrim(rtrim(@sItem)) + @sTmp
                    END                  
                 if (@nGetItem = '0')
                    BEGIN
                     select @sQty = ltrim(rtrim(@sQty)) + @sTmp
                    END
              END   



       /* increment our loop counter and move on to 
          the next value in the items_ordered_Array 
          string */


           select @nCnt = @nCnt + 1


        END



       /* if we get here (after the while loop), 
          all has gone well and we can commit the transaction */


     COMMIT TRANSACTION AddOrderTrans
     goto OnExit
  END


 
  OnErrorTrap:

 /* oops!!! we ran into a problem.  Return the 
    SQL Server error code to ADO and rollback
    the transaction */ 

  
   select @returnval = @@Error 
   ROLLBACK TRANSACTION AddOrderTrans

          
  OnExit:
  RETURN @returnval
GO
There you have it!  It wasn't particularly difficult but very effective in handling this type of scenario.  Feel free to modify the code to fit your particular style, standard, or need.

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.