Passing lists to SQL server stored procedures

One much needed feature missing from SQL Server 2005 is the ability to pass "a list of values" from .Net as a parameter to a T-SQL based stored procedure. Loads of scenarios spring to mind but here's a couple of obvious ones: 

• INSERT a list of values into the database in one "chunky" call (e.g. some IDs from a CheckBoxList)
• SELECT rows where IDs are IN (<list of IDs>)

You get the idea. Taking the INSERT as an example, there are various approaches you can adopt to achieve the desired result:

• Use dynamic SQL! I'm not even going to talk about this as this blog entry is on stored procs and dynamic SQL is rarely the ideal solution for obvious reasons ;-)
• Make a stored proc call for each ID to insert. This is the most common approach I see in various projects, mainly because it is the easiest to implement. The drawback of course is if I were to insert 60 values, it would result in 60 "chatty" calls to the database. Ummm... me hates chatty repetitive db calls plz.
• Pass comma separated values via a VARCHAR (or similar) parameter. This works fine but has messy "string splitting" in the stored procedure to extract the IDs and then build the SQL statement in the procedure itself. Prone to SQL injection and not the best performance.
• Pass the list as an XML parameter. This is nicer and is my preferred option (see below)

There are other approaches too (see a detailed description of arrays and lists in sql server by Erland Sommarskog).

Using XML

Using XML for "list passing" has a number of benefits, in particular the ability to pass lists of more "complex types" rather than just single values.  Here's a stored procedure I'm using in a current project (I've stripped out irrelevant code) which takes Study data and inserts it in "one set based query".

CREATE PROCEDURE [dbo].[Study_SaveData](
    @subjectStudyID int,
    @entryAB char(1),
    @studyDataXML XML
    )
AS
BEGIN
    INSERT INTO StudyData (subjectStudyID, entryAB, studyParID, tpID, dataValue)
    SELECT    @subjectStudyID AS SubjectStudyID,
            @entryAB AS EntryAB,
            StudyTab.StudyCol.value('StudyParID[1]','int') AS StudyParID,
            StudyTab.StudyCol.value('TPID[1]','int') AS TPID,
            StudyTab.StudyCol.value('DataValue[1]','float') AS DataValue
    FROM @studyDataXML.nodes('//StudyDataList/StudyData') AS StudyTab(StudyCol)
END

To call this in T-SQL, you would have something like this:

EXEC    [dbo].[Study_SaveData]
        @subjectStudyID = 34,
        @entryAB = 'A',
        @studyDataXML = '<StudyDataList><StudyData><StudyParID>931</StudyParID><TPID>2732</TPID><DataValue>1</DataValue></StudyData>
<StudyData><StudyParID>931</StudyParID><TPID>2733</TPID><DataValue>2</DataValue></StudyData>
<StudyData><StudyParID>931</StudyParID><TPID>2734</TPID><DataValue>3</DataValue></StudyData>
</StudyDataList>'

In your application's DAL layer, your C# calling code could be (again code simplified for brevity):

public static void SaveData(StudyData studyData)
 {
     DBHelper DBH = new DBHelper();
     DBH.AddParameter("@subjectStudyID", studyData.SubjectStudyID);
     DBH.AddParameter("@entryAB", studyData.EntryAB);
     // pass all data values in one go to avoid 'orrible chatty round trips ;-)
     DBH.AddParameter("@studyDataXML", GetStudyDataXMLString(studyData));
     DBH.ExecuteNonQuery("Study_SaveData", CommandType.StoredProcedure);
 }

which calls the method below to translate the DataValueList property (utilising generics) into an XML string:

 private static string GetStudyDataXMLString(StudyData studyData)
 {
     StringBuilder XMLString = new StringBuilder();
     XMLString.AppendFormat("<StudyDataList>");
     foreach (KeyValuePair<StudyData.StudyDataKey, double?> SDKV in studyData.DataValueList)
     {
         XMLString.AppendFormat("<StudyData>");
         XMLString.AppendFormat("<StudyParID>{0}</StudyParID>", SDKV.Key.StudyParID);
         XMLString.AppendFormat("<TPID>{0}</TPID>", SDKV.Key.TpID);
         XMLString.AppendFormat("<DataValue>{0}</DataValue>", SDKV.Value);
         XMLString.AppendFormat("</StudyData>");
     }
     XMLString.AppendFormat("</StudyDataList>");
     return XMLString.ToString();
 }

You get the idea. studyData.DataValueList contains the "list data" to be passed in and inserted into the database. I used StringBuilder for the xml concatenation as in this case I think it fits the bill but purists might prefer an XmlTextWriter approach.

This will ding dang do then! I don't think the code needs detailed explanation, but comments and queries welcome.

In summary, it performs very well and is adaptable for various lists of objects and more complex structures.

Clarkey

Useful Refs

• Arrays and lists in sql server by Erland Sommarskog
• Jon Galloway's XML param passing article (a lot of my initial code was based on snippets from this)