passing list of name/value pairs to stored procedure

Question

I have a name/value pair in a List<T> and needing to find the best way to pass these to a stored procedure.

Id   Name
1    abc
2    bbc
3    cnn
....
...

What is the best way to accomplish this?

Answer 1

Take a look at Arrays and Lists in SQL Server 2008 to get some ideas

SQL Server 2008 also supports this multi row values syntax

create table #bla (id int, somename varchar(50))

insert #bla values(1,'test1'),(2,'Test2')

select * from #bla

Answer 2

One way to handle this in SQL Server 2005 (prior to the availability of table valued parameters) was to pass a delimited list and use a Split function. If you are using a two-column array, you would want to use two different delimiters:

Declare @Values varchar(max)
Set @Values = '1,abc|2,bbc|3,cnn'

With SplitItems As
    (
    Select S.Value As [Key]
        , S2.Value
        , Row_Number() Over ( Partition By S.Position Order By S2.Position ) As ElementNum
    From dbo.Split(@Values,'|') As S
        Outer Apply dbo.Split(S.Value, ',') As S2
    )
Select [Key]
    , Min( Case When S.ElementNum = 1 Then S.Value End ) As ListKey
    , Min( Case When S.ElementNum = 2 Then S.Value End ) As ListValue
From SplitItems As S
Group By [Key]

Create Function [dbo].[Split]
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , Len(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.columns As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value < Len(CL.List)
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

Another way to handle this without table-valued parameters is to pass Xml as an nvarchar(max):

Declare @Values nvarchar(max)
Set @Values = '<root><Item Key="1" Value="abc"/>
<Item Key="2" Value="bbc"/>
<Item Key="3" Value="cnn"/></root>'

Declare @docHandle int
exec sp_xml_preparedocument @docHandle output, @Values

Select *
From OpenXml(@docHandle, N'/root/Item', 1) 
    With( [Key] int, Value varchar(10) )

Answer 3

i endup using foreach <insert>