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
– Lukasz Szozda, Commented Sep 12, 2015 at 18:50
Check answers here C# SQL Server - Passing a list to a stored procedure — T.S.
– T.S., Commented Sep 13, 2015 at 3:31
possible duplicate of Adding multiple parameterized variables to a database in c# — Zohar Peled
– Zohar Peled, Commented Sep 13, 2015 at 7: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] (stackoverflow.com/questions/23679594/…) — g5thomas
– g5thomas, Commented Sep 13, 2015 at 17:31

Gottfried Lesigang · Accepted Answer · 2015-09-13 08:27:52Z

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, 2015 at 8:27

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zohar Peled Over a year ago

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?

Gottfried Lesigang Over a year ago

@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...

Gottfried Lesigang Over a year ago

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.

Zohar Peled Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related