How to pass a StringList as part of SqlParameter to obtain results from SqlServer

Question

I am trying to execute a sql query by passing a lists of keys as a parameter of keycodelist, but it doesn't work. I got KeyNotFoundException even though the value exists in database.

How should I fix this issue? Do I need to escape the parameter calling getKeyValue?

I called the function like this.

getKeyValue("'username', 'userid'")


    private Function getKeyValue(ByVal keyList as String) 
          Dim output As Dictionary (Of String, String)
          Dim systemkeysql As String = "select key_code, key_value from system_key_table where key_code in (@keycodelist) "

                Try
                    Using connection As New SqlConnection(getConnectionString())
                        Using command As New SqlCommand(systemkeysql, connection)

                            With (command.Parameters)
                                .Add(New SqlParameter("@keycodelist", System.Data.SqlDbType.NVarChar)).Value = keylist
                            End With

                            connection.Open()
                            Using reader As SqlDataReader = command.ExecuteReader()
                                While reader.Read()
                                    output.Add(reader(0), reader(1))

                                End While
                            End Using

                        End Using
                        connection.Close()
                    End Using

                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.OkOnly)
                End Try 
        Return Output
End Function

Shortest answer use **Table-Valued Parameters** `SELECT * FROM ... WHERE keycode IN (SELECT t.keycode FROM @tvp t)` With your code you do sth like this: `SELECT * FROM ... WHERE keycode IN ('key1,key2,key3')` and you want `SELECT * FROM ... WHERE keycode IN ('key1', 'key2', 'key3')` — Lukasz Szozda, Sep 12 '15 at 18:50
Check answers here [C# SQL Server - Passing a list to a stored procedure](http://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure) — T.S., Sep 13 '15 at 03:31
possible duplicate of [Adding multiple parameterized variables to a database in c#](http://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp) — Zohar Peled, Sep 13 '15 at 07:37
Thanks for you guy's help. Good knowledge to keep in mind. I would need to talk to DBA to grant the create permission for this particular userid. See [link] (http://stackoverflow.com/questions/23679594/what-permission-do-i-need-to-use-an-sql-server-table-valued-parameter-tvp-as-a) — g5thomas, Sep 13 '15 at 17:31

score 1 · Answer 1 · answered Sep 13 '15 at 08:27

1

One reason, why I do not like the "oh we had this before, it's a duplicate" thing is, that the linked articles can be quite old and the answers accepted and/or with the highest score might not be the best today... This approach is listed on these pages too, but very low scored. In my opinion it is the fastest and most elegant way to deal with this (at least for one like me avoiding dynamic SQL whenever possible...)

DECLARE @tbl TABLE(ID INT, SomeValue VARCHAR(10));
INSERT INTO @tbl VALUES
 (1,'value 1')
,(2,'value 2')
,(3,'value 3')
,(4,'value 4')
,(5,'value 5');

DECLARE @ListOfIDs VARCHAR(20)='1,3,5';

WITH ListOfIDs AS
(
    SELECT CAST('<root><r>'+REPLACE(@ListOfIDs,',','</r><r>')+'</r></root>' AS XML) AS IDsAsXML
)
,ListOfIDsResolved AS
(
    SELECT x.y.value('.','int') AS ID
    FROM ListOfIDs
    CROSS APPLY ListOfIDs.IDsAsXML.nodes('/root/r') x(y)
)
SELECT * FROM @tbl AS tbl
INNER JOIN ListOfIDsResolved ON tbl.ID=ListOfIDsResolved.ID

answered Sep 13 '15 at 08:27

Shnugo

66,100
9
53
114

so, you rather pass delimited values to the database and split them instead of passing a table valued parameter? elegance is often a matter of opinion, but what is the fastest was is testable. care to create a benchmark and compare your splitting technique against a table valued parameter? – Zohar Peled Sep 13 '15 at 09:23
@ZoharPeled, OK, sorry for my wording, this was a bit to enthusiastic:-) The approach with a table valued parameter is very nice too. Both will lead to an inner join between a quite small one-value-list against a (probably indexed) table column. Neither the splitting, nor the overhead of a TVP will be of significant cost. I prefer solutions which do not depend on structures like a created type. I should have marked my part as opinion more clearly... – Shnugo Sep 13 '15 at 19:09
Hi Zohar, one more point: I just found @g5thomas comment about the "create permission" he must speak with the DBA about. That could be a reason to prefer inline approaches. – Shnugo Sep 13 '15 at 19:14
In the case where there are no pernissions to create types, I would probalby go with building the sql and it's parameters dynamically in the .net code and not use a splitter function in sql. I can't create a sample for this now, perhaps in a few days. – Zohar Peled Sep 13 '15 at 20:31

How to pass a StringList as part of SqlParameter to obtain results from SqlServer

1 Answers1