How to determined the number of parameter in a stored procedures

How to determined the number of parameter in a stored procedures

To determined the number of parameter in a stored procedures

Here’s the code to display the stored procedure called sp_MyStoredProc.

<%@ LANGUAGE = VBScript %>

Stored Proc Example

<%

Set Conn = Server.CreateObject("ADODB.Connection")

' The following line must be changed to reflect your data source info

Conn.Open "data source name", "user id", "password"

set cmd = Server.CreateObject("ADODB.Command")

set cmd.ActiveConnection = Conn

' Specify the name of the stored procedure you wish to call

cmd.CommandText = "sp_MyStoredProc"

cmd.CommandType = adCmdStoredProc

' Query the server for what the parameters are

cmd.Parameters.Refresh

%>

<% For Each param In cmd.Parameters %>

<%

Next

Conn.Close

%>

PARAMETER NAME	DATA-TYPE	DIRECTION	DATA-SIZE
<%= param.name %>	<%= param.type %>	<%= param.direction %>	<%= param.size %>

An example of output might look like this

PARAMETER NAME DATA-TYPE DIRECTION DATA-SIZE

Return_Value 3 4 0

param1 129 1 30

Please note that without including the adovbs.inc you are not able to use the constanst such as these.

'---- ParameterDirectionEnum Values ----

Const adParamInput = &H0001

Const adParamReturnValue = &H0004

'---- DataTypeEnum Values ----

Const adInteger = 3

Const adChar = 129

But you can use the numeric value associated with it.