Are there any built in SQL Server 2005 functions to serialize / deserialize string parameters to a table?

Question

The big question is are there any "built in" SQL Server 2005 functions to serialize / deserialize string parameters to a table variables? The rest explains my need further, but may not be necessary.

I have a SQL SP I would like to refactor. It currently accepts five customers ids and five sets of order ids relevant to those customers. (one to many).

I would like to change the parameters to select one or more customer ids each having one or more order ids.

The customer id list would be easy because you can provide a string "1,3,5,77,21", then dbo.split(@customer, ',') (then isnumeric() and convert(int)..)

Pages would be more difficult. My first thought is a string like "[1, 3, 5], [2, 4, 6], [2, 4, 6]". Of course, I could cheat and do a double dbo.split() on something like "1,3,5|2,4,6|2,4,6". However, I would want something a little more humanized and standard, maybe XML?

Answer 1

Why not try to use the XML data type

Understanding the SQL Server 2005 XML Data Type

and

XML Support in Microsoft SQL Server 2005

Answer 2

Using custom table expressions (CTE) you can convert the list to a table much more easily. You may be able to adapt this pattern to create the nested data you are considering.

DECLARE
    @cust_list  VARCHAR(1000)

SET @cust_list = '111,222,333,444,555'

;WITH cust_tbl(i,j)
AS
(
    SELECT  i = 1,
            j = CHARINDEX (',', @cust_list + ',')
    UNION ALL
    SELECT  i = j + 1,
            j = CHARINDEX (',', @cust_list + ',', j + 1)
      FROM  cust_tbl
     WHERE  CHARINDEX (',', @cust_list + ',', j + 1) <> 0
)
SELECT  SUBSTRING(@cust_list, i, j - i) AS CustId
  FROM  cust_tbl
GO

CustId
-------
111
222
333
444
555

(5 row(s) affected)