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