CODEWITHSUNDEEP
sql-servervb.net
g5thomas
g5thomas

Reputation: 315

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

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

Upvotes: 2

Views: 2072

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 1

Related Questions