Comma-separated Value Insertion In Sql Server 2005

How can I insert values from a comma-separated input parameter with a stored procedure? For example: exec StoredProcedure Name 17,'127,204,110,198',7,'162,170,163,170' you can see

Solution 1:

Have a lok at something like (Full Example)

DECLARE@InsertsTABLE(
        ID INT,
        Val1 INT,
        Val2 INT,
        Val3 INT
)

DECLARE@Param1INT,
        @Param2VARCHAR(100),
        @Param3INT,
        @Param4VARCHAR(100)

SELECT@Param1=17,
        @Param2='127,204,110,198',
        @Param3=7,
        @Param4='162,170,163,170'DECLARE@Table1TABLE(
        ID INTIDENTITY(1,1),
        Val INT
)

DECLARE@Table2TABLE(
        ID INTIDENTITY(1,1),
        Val INT
)

DECLARE@textXML XML        

SELECT@textXML=CAST('<d>'+ REPLACE(@Param2, ',', '</d><d>') +'</d>'AS XML)
INSERTINTO@Table1SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM@textXML.nodes('/d') T(split)

SELECT@textXML=CAST('<d>'+ REPLACE(@Param4, ',', '</d><d>') +'</d>'AS XML)
INSERTINTO@Table2SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM@textXML.nodes('/d') T(split)

INSERTINTO@InsertsSELECT@Param1,
        t1.Val,
        @Param3,
        t2.Val
FROM@Table1 t1 INNERJOIN@Table2 t2 ON t1.ID = t2.ID

SELECT*FROM@Inserts

Solution 2:

You need a way to split and process the string in TSQL, there are many ways to do this. This article covers the PROs and CONs of just about every method:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

You need to create a split function. This is how a split function can be used:

SELECT*FROM YourTable                               y
    INNERJOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSSJOIN sys.objects s2
ALTERTABLE Numbers ADDCONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATEFUNCTION[dbo].[FN_ListToTableRows]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNSTABLEASRETURN 
(
    ----------------
    --SINGLE QUERY-- --this will return empty rows, and row numbers
    ----------------
    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
);
GO

You can now easily split a CSV string into a table and join on it. To accomplish your task, set up a test table to insert into:

createtable YourTable (col1 int, col2 int)

then create your procedure:

CREATEPROCEDURE StoredProcedureName
(
     @Params1int
    ,@Array1varchar(8000)
    ,@Params2int
    ,@Array2varchar(8000)
)
ASINSERTINTO YourTable
        (col1, col2)
    SELECT
        a1.ListValue, a2.ListValue
        FROM dbo.FN_ListToTableRows(',',@Array1)            a1
            INNERJOIN dbo.FN_ListToTableRows(',',@Array2)  a2 ON a1.RowNumber=a2.RowNumber
GO

test it out:

exec StoredProcedureName 17,'127,204,110,198',7,'162,170,163,170'select*from YourTable

OUTPUT:

(4row(s) affected)
col1        col2
----------- -----------127162204170110163198170

(4row(s) affected)

Solution 3:

This may not be an answer to your question... But I thought of letting you know that there is a better way to pass related values (Table Format) to a stored procedure... XML... You can build the XML string in your app (just as regular string) and pass it on to the stored procedure as a parameter... You can then use the following syntax to get it into a table. Hope this helps... In this way you can pass an entire table as parameter to stored procedure...

--Parameters@param1int,
    @Budgets xml,
    @Param2int-- @Budgets   = '<Values><Row><Val1>127</Val1><Val2>162</Val2></Row>  <Row><Val1>204</Val1><Val2>170</Val2></Row></Values>'SELECT@param1as Param1, 
        x.query('Val1').value('.','int') as val1, 
        @param3as Param3,
        x.query('Val2').value('.','int') as val1, 
    into #NewTable
    FROM@Budgets.nodes('/Values/Row') x1(x)