Sane/fast method to pass variable parameter lists

Question

A fairly comprehensive query of the brain has turned up a thousand and one ways to pass variable length parameter lists that involve such methods as:

• CLR based methods for parsing strings to lists of integers
• Table valued functions that require the presence of a 'Numbers' table (wtf?)
• Passing the data as XML

Our requirements are to pass two variable length lists of integers (~max 20 ints) to a stored procedure. All methods outlined above seem to smell funny.

Is this just the way it has to be done, or is there a better way?

Edit: I've just found this, which may qualify this question as a dupe

Answer 1

Yes, I'd definitely look at Table Valued Parameters for this. As a side benefit, it may allow you to use a nice, clean set-based implementation for the innards of your procedure directly, without any data massaging required.

Here's another reference as well...

Answer 2

Here is a fairly fast method to split strings using only T-SQL and you input parameter is only a string. You need to have a table and a function (as described below) already set up to use this method.

create this table:

CREATE TABLE Numbers (Number  int not null primary key identity(1,1))
DECLARE @n int
SET @n=1
SET IDENTITY_INSERT Numbers ON
WHILE @N<=8000
BEGIN
    INSERT INTO Numbers (Number) values (@n)
    SET @n=@n+1
END
SET IDENTITY_INSERT Numbers OFF

create this function to split the string array (I have other versions, where empty sections are eliminated and ones that do not return row numbers):

CREATE FUNCTION [dbo].[FN_ListAllToNumberTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    RowNumber int             --REQUIRED, the list to split apart
   ,ListValue varchar(500)    --OPTIONAL, the character to split the @List string on, defaults to a comma ","

)
AS
BEGIN

--this will return empty rows, and row numbers
INSERT INTO @ParsedList
        (RowNumber,ListValue)
    SELECT
        ROW_NUMBER() OVER(ORDER BY number) AS RowNumber
            ,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue
        FROM (
                 SELECT @SplitOn + @List + @SplitOn AS ListValue
             ) AS InnerQuery
            INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue)
        WHERE SUBSTRING(ListValue, number, 1) = @SplitOn

RETURN

END 
go

here is an example of how to split the parameter apart:

CREATE PROCEDURE TestPass
(
    @ArrayOfInts    varchar(255)  --pipe "|" separated list of IDs
)
AS

SET NOCOUNT ON

DECLARE @TableIDs  TABLE (RowNumber int, IDValue int null)

INSERT INTO @TableIDs (RowNumber, IDValue)  SELECT RowNumber,CASE WHEN LEN(ListValue)<1 then NULL ELSE  ListValue END FROM dbo.FN_ListAllToNumberTable('|',@ArrayOfInts)

SELECT * FROM @TableIDs
go

this is based on: http://www.sommarskog.se/arrays-in-sql.html