Geeks With Blogs
Doug Butscher Nothin' but .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
  10:     ' pull out the create proc statement
  11:     spText = spText.Replace(String.Format("CREATE PROC {0}", spName), "")
  12:  
  13:     ' now we need to parse out the params themselves
  14:     Dim startIndex As Integer, endIndex As Integer
  15:     Dim params As New List(Of String)
  16:     Dim param As String
  17:  
  18:     While True
  19:         ' find the @
  20:         startIndex = spText.IndexOf("@")
  21:         If startIndex <= 0 Then
  22:             Exit While
  23:         End If
  24:         ' trim away anything to the left of the @
  25:         spText = spText.Substring(startIndex, spText.Length - startIndex)
  26:         ' find the space after the param
  27:         endIndex = spText.IndexOf(" ")
  28:         ' get the param name
  29:         param = spText.Substring(0, endIndex)
  30:         ' add to list
  31:         params.Add(param)
  32:         ' rip out that param we just found
  33:         spText = spText.Substring(endIndex, spText.Length - endIndex)
  34:         ' look for the next @, to see if it's a param, or just sql code down stream
  35:         startIndex = spText.IndexOf("@")
  36:         ' look for a comma, to see if there are more params listed
  37:         endIndex = spText.IndexOf(",")
  38:         ' if the comma comes after the param, this is the last param
  39:         If endIndex > startIndex Then
  40:             ' this is the last parm left
  41:             Exit While
  42:         End If
  43:  
  44:     End While
  45:  
  46:     Return params
  47: End Function
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
Requesting Gravatar...
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

# re: How to query a stored procedure for input parameters in .NET
Requesting Gravatar...
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

# re: How to query a stored procedure for input parameters in .NET
Requesting Gravatar...
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

# re: How to query a stored procedure for input parameters in .NET
Requesting Gravatar...
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

# re: How to query a stored procedure for input parameters in .NET
Requesting Gravatar...
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

Your comment:
 (will show your gravatar)


Copyright © Doug Butscher | Powered by: GeeksWithBlogs.net