Click here to Skip to main content
15,117,095 members
Articles / Programming Languages / C#
Posted 27 Sep 2003


178 bookmarked

Passing an array or DataTable into a stored procedure

Rate me:
Please Sign up or sign in to vote.
4.82/5 (91 votes)
27 Sep 20034 min read
This article describes how to pass the equivalent of an array, into a stored procedure, showing a number of different data types. This technique doesn't have the size limitations that a number of other techniques suffer from.


Have you ever wanted to pass a DataTable, or array, containing tens or tens of thousands (or even millions) of records, into a SQL Server2000 stored procedure in just one database call? Ever wanted to pass a list of IDs of records to select or delete into a SP? Then read on.

The trick is to convert lists of data into byte arrays, which are then passed as Image type parameters into the stored procedure, then in the SP the image is transformed into a table variable containing the original data. The following SQL server function is one of a number of functions I have written to easily transform an Image parameter into a variable table. This one creates a table of varchars.

CREATE FUNCTION dbo.GetTableVarchar(@Data image)
RETURNS @DataTable TABLE (RowID int primary key IDENTITY , 
                                          Value Varchar(8000)) 
      --First Test the data is of type Varchar.
      IF(dbo.ValidateExpectedType(103, @Data)<>1) RETURN
      --Loop thru the list inserting each
      -- item into the variable table.
      DECLARE @Ptr int, @Length int, 
         @VarcharLength smallint, @Value Varchar(8000)
      SELECT @Length = DataLength(@Data), @Ptr = 2
            --The first 2 bytes of each item is the length of the 
            --varchar, a negative number designates a null value.
            SET @VarcharLength = SUBSTRING(@Data, @ptr, 2)
            SET @Ptr = @Ptr + 2
                  SET @Value = NULL
               SET @Value = SUBSTRING(@Data, @ptr, @VarcharLength)
               SET @Ptr = @Ptr + @VarcharLength
            INSERT INTO @DataTable (Value) VALUES(@Value)

I have also created a number of C# classes for creating byte arrays from collects of different types of data.

Calls to the database, in the following examples, use SQLHelper from the Microsoft Application Blocks, as these hide most of the dull plumbing required to call a SP. MSDN Website.

Please bear with my humble snail farm examples; I couldn’t bring myself to use customer/orders examples.

We’ll start off with a simple case for the first example.

Passing a list of integer IDs to select records with

Code to take the checked items in a checked list box and pass their IDs in to a stored procedure. The SP then returns a result set containing the details for each selected ID.

private DataSet GetSelectedSnailDetails()
   SQLIntListCreator snailIDs = new SQLIntListCreator();
   foreach(Snail aSnail in mySelectionCheckedListBox.CheckedItems)
   return SQLHelper.ExecuteDataset(CONN_STRING, 
           "GetSnailDetails", snailIDs.GetList());

Stored procedure to take a list of ints in the form of an image (byte array), convert it to a table variable of ints which is then joined on the SnailID column of the Snails table to return details for each selected snail.

      @SnailIDs   image
      SELECT Snail.* 
FROM dbo.GetTableInt(@SnailIDs) SIDs 
INNER JOIN Snails ON SIDs.Value = Snails.SnailID

Stored procedure to delete all the snails in the @Snails image list..

      @SnailIDs   image
      DELETE Snail 
      FROM dbo.GetTableInt(@SnailIDs) SIDs 
      INNER JOIN Snails ON SIDs.Value = Snails.SnailID

Passing a collection of snail statuses to a SP for updating

private void UpdateSnailStatuses(Snail[] snails)
   SQLIntListCreator snailIDs = new SQLIntListCreator();
   SQLVarcharListCreator statuses = 
                new SQLVarcharListCreator();
   SQLRowversionListCreator rowVersions= 
                new SQLRowversionListCreator();
   foreach(Snail aSnail in snails)
       "UpdateSnailStatuses", snailIDs.GetList(), 
       statuses.GetList(), rowVersions.GetList());

Stored procedure to update the statuses of snails while also checking for the correct timestamp rowversion.

CREATE PROCEDURE dbo.UpdateSnailStatuses
      @SnailIDs   image, 
      @Statuses   image, 
      @RowVersions image
      UPDATE Snail
      SET Snail.Status = Statuses.Value
      FROM Snail 
INNER JOIN GetTableInt(@SnailIDs) SnailIDs 
               ON Snail.SnailID = SnailIDs.Value
INNER JOIN GetTableVarchar(@Statuses) Statuses 
               ON SnailIDs.RowID = Statuses.RowID
INNER JOIN GetTableRowversion(@RowVersions) RowVersions 
               ON Statuses.RowID = RowVersions.RowID
      WHERE TSEQUAL(Snail.RowVersion, RowVersions.Value) 

Passing a DataTable to a SP for insertion into a table

Code to convert the data in a DataTable into 5 byte arrays, which are then passed to the AddSnails stored procedure to be inserted into the Snail table.

private void AddSnails(DataTable snails)
      SQLVarcharListCreator species = new SQLVarcharListCreator();
      SQLDatetimeListCreator dOB = new SQLDatetimeListCreator();
      SQLNvarcharListCreator location = new SQLNvarcharListCreator();
      SQLBitListCreator isMale = new SQLBitListCreator();
      SQLMoneyListCreator value = new SQLMoneyListCreator();
      foreach(DataRow row in snails.Rows)
      //A command is explicitly created here 
      //instead of using SQLHelper as we need to be 
      // able to set the CommandTimeout property 
      //big enough to insert 1 000 000 records.
      using(SqlConnection cnn = new SqlConnection(CONN_STRING))
         SqlCommand comInst = new SqlCommand("AddSnails", cnn);
         comInst.CommandType = CommandType.StoredProcedure;
         comInst.CommandTimeout = 10000;
         comInst.Parameters.Add("@Species", species.GetListAndReset());
         comInst.Parameters.Add("@DOB", dOB.GetListAndReset());
         comInst.Parameters.Add("@Location", location.GetListAndReset());
         comInst.Parameters.Add("@IsMale", isMale.GetListAndReset());
         comInst.Parameters.Add("@Value", value.GetListAndReset());

Stored procedure to convert the images into table variables, join them together and insert the resulting data into the Snail table.

      @Species    image,
      @DOB        image,
      @Location   image,
      @IsMale     image,
      @Value      image
INSERT INTO Snail (Species, DOB, Location, IsMale, Value)
SELECT Species.Value, DOB.Value, 
                       Location.Value, IsMale.Value, Value.Value
FROM        GetTableMoney(@value) Value 
INNER JOIN  GetTableBit(@isMale) IsMale ON Value.RowID = IsMale.RowID 
INNER JOIN  GetTableVarchar(@species) Species 
                       ON IsMale.RowID = Species.RowID
INNER JOIN  GetTableDateTime(@dOB) DOB ON Species.RowID = DOB.RowID 
INNER JOIN  GetTableNvarchar(@location) Location 
                       ON DOB.RowID = Location.RowID

Design view of the join across all the table variables.

Design view of table variable join


I ran a performance comparison between inserting snail records using the above stored procedure and a standard 1 record insert stored procedure called many times. These are the results I got.

Records InsertedStandard one stored procedure call per record (msec)One stored procedure call for all records (msec)
10002 259505
10 00022 5934 083
100 000301 36844 645
1 000 0003 094 385571 020

The comparison was done on a PC running a P4 1.8 GHz processor with 512 MB RAM. This is a very crude comparison as network speed, table structure and other things will greatly influence the speed of both methods. In this example both the SQL Server and the client where running on the same machine resulting in a very fast network. Inserts into the snail table are fast as it has only one index (clustered).

Points of interest

  • Strong Type checking: Each SQLListCreator object prefixes the byte array it produces with a data type byte e.g. 107 for the SQLMoneyListCreator. The SQL function then checks this first byte and throws an error if it is of the wrong data type. As RAISERROR cannot be called from within a SQL function, the following work around is used. An attempt to convert the error message to an int is made; this results in an error being returned like this - 'Syntax error converting the varchar value'. The real error is: 'Function expected a list of Int types but was given a list of Money types.' to a column of data type int. Not the most elegant message but it points you in the right direction.
  • ToString: ToString() returns a hex version of the list, this can be useful during development/debugging when you may want to paste lists into Query Analyzer. E.g.
    SELECT C.Value, PV.Value, EC.Value, TS.Value, 
                                        P.Value, PV.Value/TS.Value
    FROM GetTableVarchar
          20572E2042757368000B52616C7068204E61646572) C 
    INNER JOIN GetTableInt(0x65030A32590301E5C2002BFD8B) 
          PV ON C.RowID = PV.RowID 
    INNER JOIN GetTableInt(0x650000010A0000010F00000000) EC ON 
          PV.RowID = EC.RowID 
    INNER JOIN GetTableMoney
          TS ON EC.RowID = TS.RowID
    INNER JOIN GetTableBit(0x6C9B) P ON TS.RowID = P.RowID
  • Data Types: I have implemented SQL functions and C# classes for BigInt, Int, Bit, Datetime, Money, RowVersion, Uniqueidentifier, Varchar and Nvarchar. Most other data types could be implemented without too much difficulty, the exceptions are ntext, text and image. These data types can contain more that 8000 bytes and as such the SQL Substring function can not be used to strip the individual items from a list of items contained in an image.
  • Nulls: Null values can be added into a list with the AddNull() method, also the AddValue(object value) overload will accept DBNull and any object that implements INullable and returns true for IsNull().
  • Packet size: The database connection packet size can be set to a larger size than the default in the connection string. This may give additional performance improvements when sending large amounts of data to the database in single calls, but will degrade performance for smaller calls.


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


About the Author

Gareth Edwards
Web Developer
New Zealand New Zealand
Works as a developer for a small software development company in Christchurch, New Zealand, were I like to go tramping/alpine climbing when not looking after the sproggs.

Comments and Discussions

GeneralThanks Pin
kevin_ze25-Apr-12 21:59
Memberkevin_ze25-Apr-12 21:59 
GeneralIf you are using Sql Server 2008 Pin
82edwards8-Apr-11 10:31
Member82edwards8-Apr-11 10:31 
GeneralMy vote of 5 Pin
A7mad_26-Jun-10 7:50
MemberA7mad_26-Jun-10 7:50 
GeneralRuns ok, but does not insert anything!! Pin
joaoh8224-Sep-09 5:45
Memberjoaoh8224-Sep-09 5:45 
GeneralDataBase tables Pin
collie346-Sep-09 6:33
Membercollie346-Sep-09 6:33 
GeneralGreat Work!!! Pin
ganesh250611-Feb-09 21:15
Memberganesh250611-Feb-09 21:15 
Questionint = substring(..) machine dependency? Pin
Jon Mcleod6-Jul-08 15:55
MemberJon Mcleod6-Jul-08 15:55 
jainvikramraj13-Mar-08 1:33
Memberjainvikramraj13-Mar-08 1:33 
GeneralCreate the Image value in T-SQL Pin
ChickiePooh11-Mar-08 6:19
MemberChickiePooh11-Mar-08 6:19 
Generaloverflow bugs Pin
sprucely21-May-07 13:43
Membersprucely21-May-07 13:43 
Questionhow to convert int[] to byte[] in C# Pin
keops31315-Oct-06 0:20
Memberkeops31315-Oct-06 0:20 
GeneralCopyright permission Pin
louisontheweb1-Jun-06 15:04
Memberlouisontheweb1-Jun-06 15:04 
QuestionHow to handle decimal values. Pin
Iftikhar Akram2-May-06 13:31
MemberIftikhar Akram2-May-06 13:31 
Generalcopyright info Pin
wengem9-Mar-06 9:14
Memberwengem9-Mar-06 9:14 
GeneralUsing XML as a parameter Pin
csa1402-Feb-06 22:17
Membercsa1402-Feb-06 22:17 
Questionare there arraies in t-sql and how to deal if exist ? Pin
imadouzoun30-Dec-05 10:44
Memberimadouzoun30-Dec-05 10:44 
General[Message Removed] Pin
Mojtaba Vali24-Nov-05 18:04
MemberMojtaba Vali24-Nov-05 18:04 
GeneralAddValue checks dataType prior to ValidateDataType Pin
MarilynB1-Nov-05 8:02
MemberMarilynB1-Nov-05 8:02 
General[Message Removed] Pin
Mojtaba Vali28-Oct-05 6:44
MemberMojtaba Vali28-Oct-05 6:44 
GeneralWhere to find dbo.ValidateExpectedType Pin
MarilynB12-Sep-05 4:08
MemberMarilynB12-Sep-05 4:08 
GeneralRe: Where to find dbo.ValidateExpectedType Pin
Gareth Edwards13-Sep-05 0:24
MemberGareth Edwards13-Sep-05 0:24 
GeneralRe: Where to find dbo.ValidateExpectedType Pin
MarilynB13-Sep-05 3:23
MemberMarilynB13-Sep-05 3:23 
GeneralGetTableBit Bug Pin
GiulioMdsl24-Aug-05 3:38
MemberGiulioMdsl24-Aug-05 3:38 
Generalfor dbo.GetTableVarchar(@Data image), you could have used text datatype Pin
Krish Nimmagadda29-Apr-05 12:01
MemberKrish Nimmagadda29-Apr-05 12:01 
GeneralRe: for dbo.GetTableVarchar(@Data image), you could have used text datatype Pin
MarilynB28-Jul-05 9:10
MemberMarilynB28-Jul-05 9:10 

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

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