Insert statement whith unknown number of values extracted from a string

Question

I have a stored procedure in a sql server 2008 db.
This stored procedure takes a parameter that is a list of max 5 comma separated integer values. This string is passed through a php sript and formatted like this:

01,02,03,14,15

These values should go in this table:

Id | Type id
-------------
1 | 1
1 | 2
1 | 3
1 | 14
1 | 15

...where id is the same and type id is one of the comma separated values.

Obviously, I could have a string like "01,02,03" with only 3 values. So I need a way to insert only 3 rows in the above table resulting in:

Id | Type id
-------------
1 | 1
1 | 2
1 | 3

I could modify either the php script or the stored procedure, so I'm open to all kinds of suggestion.

Answer 1

You could create a function to help you do this.

CREATE FUNCTION dbo.Split(@origString varchar(max), @Delimiter char(1))     
returns @temptable TABLE (items varchar(max))     
as     
begin     
    declare @idx int     
    declare @split varchar(max)     

    select @idx = 1     
        if len(@origString )<1 or @origString is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@origString)     
        if @idx!=0     
            set @split= left(@origString,@idx - 1)     
        else     
            set @split= @origString

        if(len(@split)>0)
            insert into @temptable(Items) values(@split)     

        set @origString= right(@origString,len(@origString) - @idx)     
        if len(@origString) = 0 break     
    end 
return     
end

Then you can call this function to split out the parameter values:

Not 100% sure where the ID value comes from so I have declared a variable.

Declare @newId int
Set @newId = 1

Insert Into dbo.MyTable (ID, TypeId)
Select @newId, *
From dbo.Split(@ParameterValues, ',')

Answer 2

I would pass XML into SQL server, which can then easily be processed as nodeset (like a table) using OPENXML or using the nodes() function on the xml datatype. It works very well and it's quite performant, almost always faster than any handmade string processing code.

DECLARE @x xml;
SET @x = '<id>01</id><id>02</id><id>03</id><id>14</id><id>15</id>';
SELECT x.id.value('.', 'int') FROM @x.nodes('id') AS x(id);

Answer 3

If you pass in 5 arguments, you could do something like this:

-- simulation of the arguments
DECLARE @p1 INT, @p2 INT, @p3 INT, @p4 INT, @p5 int;
SELECT @p1=1, @p2=2, @p4=14;

-- Select only parameters which aren't null
SELECT @p1 AS id WHERE @p1 IS NOT NULL
UNION ALL
SELECT @p2 WHERE @p2 IS NOT NULL
UNION ALL
SELECT @p3 WHERE @p3 IS NOT NULL
UNION ALL
SELECT @p4 WHERE @p4 IS NOT NULL
UNION ALL
SELECT @p5 WHERE @p5 IS NOT NULL;