Karl Seguin

Just a couple weeks ago I was pointing to the lack of a LIMIT keyword in SQL Server 2005. Last week, I was reminded about the lack of arrays.  I have a hard time believing that a platform/language/framework/anything aimed at developers in this day and age doesn't support arrays, but we'll assume the SQL Server team had more important things to do the past 6 years.

You'll run into the array-wall frequently while doing SQL Server programming, but to me, the most obvious example is when dealing with a CheakBoxList. The CheckBoxList is a perfect example; because it's typically varying length (the user could pick any number of values). Take for example a blogging system that support "tags". An author could pick 0 or more tags and then hit the "Post" button.

private sub Post_Click(o as object, e as EventArg)
  dim post as new Post()
  post.Subject = Subject.Text
  post.Body = body.Text
  foreach item as ListItem in Tags.Items
    if (item.Selected) then
      post.Tags.Add(new Tag(item.Name, item.Value))
    end if
  next
  post.Save()
  'do something nice here...
end sub

You'll eventually end up into a method that looks like:

friend sub SavePost(post as Post)
  using connection as new SqlConnectio(GET_FROM_CONFIG)
    using command as new SqlCommand()
       'now what?
    end using
  end using
end sub

It'd be sweet if we could pass in an array of TagIds, like:

command.Parameters.Add("@TagIds", SqlDbType.Int()).Value = post.TagIds

But we can't.  One solution might be to write some dynamic SQL...but that's no fun to maintain. Another is to hit the database multiple times:

foreach tag as Tag in post.Tags
   SaveTag(post.Id, tag.Id)
next

This isn't a bad idea, and can even be done in a single connection.Open(). I prefer a third solution though. In SQL Server 2000, I would pass in a comma-delimited string and convert it into a table, something like:

CREATE PROCEDURE SavePost
(
  ...
  @Tags VARCHAR(2000)
)
AS
SET NOCOUNT ON

   DECLARE @tagIds Table
   SELECT @tagIds = dbo.CsvToInt(@Tags)
 
 
   --INSERT THE POST
   DECLARE @postId INT
   SET @postId = SCOPE_IDENTITY()
   
   INSERT INTO PostTags
      SELECT @postId, [value]
         FORM @tagIds

SET NOCOUNT OFF

Here's what my CsvToInt function might look like:
CREATE  Function dbo.CsvToInt
(
   @Array VARCHAR(2000)
)
RETURNS @IntTable TABLE (IntValue INT)
AS
BEGIN
  IF @Array <> '' BEGIN
    DECLARE @separator char(1)
    SET @separator = ','

    DECLARE @separator_position INT
    DECLARE @array_value VARCHAR(2000)

    SET @array = @array + ','
    WHILE patindex('%,%' , @array) <> 0 BEGIN
      SELECT @separator_position =  patindex('%,%' , @array)
      SELECT @array_value = LEFT(@array, @separator_position - 1)
      INSERT @IntTable Values (CAST(@array_value AS INT))
      SELECT @array = stuff(@array, 1, @separator_position, '')
    END
  END
  RETURN
END


This solution has a number of limitations. The first being the length which might prove problematic in some situations. Worse though is that it only really works for comma delimited integers. What happens if you want to pass in a more complexe structure, like a name=>value collection.

Well, with SQL Server 2005, things have gotten a lot better. Instead of passing in a CSV, we can leverage the SQL Server's XML capabilities.  Changing the paramters of what we are trying to do (from a simple csv, to a key=>name bulk insert), we first need to convert our collection into some XML:

friend shared function NameValueToXml(data as NameValueCollection) as string
  dim sb as new StringBuilder("<data>")
  foreach key as string in data
    sb.Append("<meta>")
    sb.AppendFormat("<key>{0}</key>", key)
    sb.AppendFormat("<value>{0}</value>",data(key))
    sb.Append("</meta>")
  next
end function

Which means we'll end up with a string that looks like:
<data>
  <meta>
    <key>Some Key</key>
    <value>Some Value</value>
  </meta>
  <meta>
    <key>Some Key 1</key>
    <value>Some Value 1</value>
  </meta>
  <meta>
    <key>Some Key 2</key>
    <value>Some Value 2</value>
  </meta>
</data>

Next, we pass the value into stored procedure:

command.Parameters.Add("@Tags", SqlDbType.Xml).Value = NameValueToXml(post.Tags)

And finally, we store the values into our table:

CREATE PROCEDURE SavePost
(
  ...
  @Tags XML
)
AS
SET NOCOUNT ON
   
   ...

   INSERT INTO XX
      SELECT ItemData.row.value('key[1]', 'varchar(200)'),
             ItemData.row.value('value[1]', 'varchar(200)')
         FROM @Tags.nodes('/data/meta') ItemData(row)

SET NOCOUNT OFF


The syntax isn't the most obvious. In my mind, it creates a node at ItemData.row for each child of /data/meta which can be access via the value method. Note that you can also access attributes by using the @name syntax.