5 Comments

• I agree that there are pro's and con's to this. This still feels sloppy to me, just a little less sloppy than the alternatives.
  

• I agree...way less sloppy than parsing commas or other separators. :-) Until we can pass a "true" CLR object as a parameter I personally think this is a nice work-around to the issue though.
  
  I'm a big fan of XML serialization/deserialization and this plays nicely with it for both input and output parameters. Multiple objects with multiple properties could be passed in as XML and then parsed and inserted, deleted or updated with a single sproc rather than making multiple calls like many apps seem to do.
  

• Not saying it is better, but I don't understand why Xml is "way less sloppy." Xml is pretty verbose, I tend to avoid it.
  
  We wrote a function at work that returns a Table variable. This way you can even do joins with it.
  
  
  Create Function [dbo].[fn_ParseDelimitedStrings]
  (@String nvarchar(3500), @Delimiter char(1))
  Returns @Values Table
  (
  RowId int Not Null Identity(1,1) Primary Key
  ,Value nvarchar(255) Not Null
  )
  As
  Begin
  
  Declare @startPos smallint
  ,@endPos smallint
  
  If (Right(@String, 1) != @Delimiter)
  Set @String = @String + @Delimiter
  Set @startPos = 1
  Set @endPos = CharIndex(@Delimiter, @String)
  
  While @endPos > 0
  Begin
  Insert @Values(Value)
  Select LTrim(RTrim(SubString(@String, @startPos, @endPos - @startPos)))
  
  -- remove the delimiter just used
  Set @String = Stuff(@String, @endPos, 1, '')
  
  -- move string pointer to next delimiter
  Set @startPos = @endPos
  
  Set @endPos = CharIndex(@Delimiter, @String)
  End
  
  Return
  
  End
  
  
  
  

• Thanks for posting the sample since it's a great example of alternative techniques.
  
  That's an awful lot of string parsing code in my opinion although I'll agree that once you've written it once it can be re-used since it's in a function. I like the ability to serialize .NET objects as XML and pass that in though and then easily parse it using the built-in XPath/XQuery functionality. XML is certainly more verbose though as you mention.
  
  

• The XML serialization method sounds nice and ill probably use it myself but it ties the business layer into the data layer. If a variable name gets changed in the business layer then the stored procedure will also need to be updated to reflect this change.