How to query a stored procedure for input parameters in .NET

I have a stored procedure that I want to call from my VB.net code. The problem is that the stored procedure input parameters may change. Rather than recompiling the stored procedure, and recompiling the assembly that calls the stored procedure, I want my VB.net assembly to query the stored procedure for its parameters, and return them back to me in a list.

I've done some blog searches for this, and I couldn't find anything that did anything like this (or my search terms were not explicit enough).

UPDATE (thanks to Bill Minton): below, I mention using the sp_helptext functionality in SQL, which is a pain. I've left the original code at the end of this post for posterity. Bill left a comment on this post that uses the following SQL functionality: select parameter_name from information_schema.parameters where specific_name = 'YourProcName'

Below is the code that uses this functionality to get back a List(Of String) with the parameters. I just coded it without testing it, so you may need to work on it a bit (I'm working on something else higher priority at the moment).

        1: Public Shared Function GetStoredProcParams(ByVal spName As String, ByVal dataAction As Aptify.Framework.DataServices.DataAction) As List(Of String)

       2:     Dim reader As SqlDataReader

       3:     Dim params As New List(Of String)

       4:  

       5:     ' build the SQL query

       6:     Dim sql As String = String.Format("select parameter_name from information_schema.parameters where specific_name = '{0}'", spName)

       7:     Try

       8:         reader = dataAction.ExecuteDataReader(sql)

       9:         While reader.Read

      10:             params.Add(reader.GetString(0))

      11:         End While

      12:         reader.Close

      13:     Catch ex As Exception

      14:         Return Nothing

      15:     End Try

      16:  

      17:     Return params

      18: End Function

Thanks again to Bill!

Old way:

I've written VB.net solution to this, which pretty much makes a call to SQL's sp_helptext stored procedure, and parses through the parameter list until it reaches [what it thinks is] the end.

This code works for my needs, but there may be a few bugs for single-parameter stored procs or different stored procedure definitions. My goal is to give a starting point to anyone else out there who is in the same boat as me, but doesn't want to spend the hour or two doing the proof-of-concept.

That being said... here's the code... Let me know if you have any questions. Note that I didn't code out the SQL connection info; you'll need to code that yourself.

       1: Public Shared Function GetStoredProcParams(ByVal spName As String) As List(Of String)

       2:     Dim sql As String = String.Format("sp_helptext {0}", spName)

       3:     Dim ds As DataSet = dataAction.GetDataSet(sql)

       4:     ' get the first two rows from the DataSet

       5:     ' (you can keep appending more rows to the spText string if you have a lot of input parms)

       6:     Dim spText As String = ds.Tables(0).Rows(0).Item(0)

       7:     spText += ds.Tables(0).Rows(1).Item(0)

       8:  

       9:     ' we should have the sp definition, with all of the input parameters

    ' pull out the create proc statement

    spText = spText.Replace(String.Format("CREATE PROC {0}", spName), "")

  

    ' now we need to parse out the params themselves

    Dim startIndex As Integer, endIndex As Integer

    Dim params As New List(Of String)

    Dim param As String

  

    While True

        ' find the @

        startIndex = spText.IndexOf("@")

        If startIndex <= 0 Then

            Exit While

        End If

        ' trim away anything to the left of the @

        spText = spText.Substring(startIndex, spText.Length - startIndex)

        ' find the space after the param

        endIndex = spText.IndexOf(" ")

        ' get the param name

        param = spText.Substring(0, endIndex)

        ' add to list

        params.Add(param)

        ' rip out that param we just found

        spText = spText.Substring(endIndex, spText.Length - endIndex)

        ' look for the next @, to see if it's a param, or just sql code down stream

        startIndex = spText.IndexOf("@")

        ' look for a comma, to see if there are more params listed

        endIndex = spText.IndexOf(",")

        ' if the comma comes after the param, this is the last param

        If endIndex > startIndex Then

            ' this is the last parm left

            Exit While

        End If

  

    End While

  

    Return params

End Function

Technorati Tags: SQL stored procedure,input parameters,how to,.net

Posted on Tuesday, April 1, 2008 11:19 AM | Back to top

Comments on this post: How to query a stored procedure for input parameters in .NET

# re: How to query a stored procedure for input parameters in .NET

This query will give you the parms for the proc in SQL2005. I can't test it with a SQL2000 box as I don't have one around me right now.

select parameter_name from information_schema.parameters where specific_name = 'YourProcName'

Left by Bill Minton on Apr 03, 2008 10:46 AM

Have you taken a look at the SqlCommandBuilder.DeriveParameters method? It will initialize the SqlCommand.Parameters collection for your stored procedure.

SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.spGetSomething";

SqlCommandBuilder.DeriveParameters(cmd);
Console.WriteLine("Count = " + cmd.Parameters.Count);

Hope that helps!
Bobby

Left by Bobby Diaz on Apr 18, 2008 7:51 PM

I had exactly the same problem trying to get a list of stored procedure parameters. The SqlCommandBuilder.DeriveParameters() call was exactly what I was looking for, and it works great.

Thanks!
-Tom

Left by Tom Phaneuf on Jul 02, 2008 3:17 AM

This part is giving me problems, "Aptify.Framework.DataServices.DataAction". Can you explain this further?

Thanks!
Mark

Left by Mark Brown on Sep 22, 2008 5:53 PM

What about simply using sp_help? It returns two tables, the second of which is the parameter list.

Left by Scott on Nov 24, 2008 7:36 PM