Vb.net Sql Variable number of Parameters

I have a function that can take two or more parameters related to database fields. It is possible for an automatic way to pass the number of desired parameters and treat them with sql parameters

 Public Shared Function GetData(ByVal User As String, ByVal Qnt As String, ..... variable number of parameters) As Boolean
        Dim conn As SqlConnection = DB.DbInit()
        Dim cmd As SqlCommand
        Dim reader As SqlDataReader
        cmd = New SqlCommand("select * from content where user=@user and Quant=@qnt", conn)
        cmd.Parameters.Add("@user", SqlDbType.NVarChar, 50)
        cmd.Parameters("@user").Value = User
        cmd.Parameters.Add("@quant", SqlDbType.Int)
        cmd.Parameters("@quant").Value = Qnt

        ' SOMETIMES I NEED MORE THEN 2 PARAMETERS (variable number of parameters)
        cmd.Parameters.Add("@Credit", SqlDbType.Decimal)
        cmd.Parameters("@Credit").Value = Credit
        cmd.Parameters.Add("@ExpDate", SqlDbType.Date)
        cmd.Parameters("@ExpDate").Value = ExpDate

        Try
            conn.Open()
            reader = cmd.ExecuteReader
            If reader.Read() Then
             .....................
            End If
            reader.Close()
        Catch err As Exception

        Finally
            conn.Close()
        End Try
    End Function

Open in new window

rflorencioAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
this is not directly supported.

workarounds include:
-use a table value parameter (http://www.emoreau.com/Entries/Articles/2010/11/SQL-Table-Value-parameters.aspx)
-use a delimited string and parse it
0
 
Carl TawnSystems and Integration DeveloperCommented:
You can add as many parameters as you like, as long as there are place holders for them in your query.
0
 
CodeCruiserConnect With a Mentor Commented:
One option is to pass this function a list of sql parameters so it just uses those parameters. Then the calling code can add any number of parameters and just pass the list to this function.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.