<%@ Language=VBScript %> <% Option Explicit Response.Expires = 0 Dim m_strProcName Dim m_strConnectionString Dim m_blnRecordset %> <% If Request.Form.Count > 0 Then m_strProcName = Trim(Request.Form("ProcName")) m_strConnectionString = Trim(Request.Form("ConnectionString")) If NOT Trim(Request.Form("ReturnRecordset")) = "" Then m_blnRecordset = True Else m_blnRecordset = False End If Call ShowCriteriaForm Call ShowProcInfo Else Call ShowCriteriaForm End If Sub ShowCriteriaForm() %>

Connection String
Procedure Name
Return A Recordset

<% End Sub Sub ShowProcInfo() 'Declare and create the command object Dim cmd Set cmd = Server.CreateObject("ADODB.Command") 'Open the connection on the command by assigning the 'connection string to the ActiveConnection property cmd.ActiveConnection = m_strConnectionString cmd.CommandType = 4 'Stored Procedure Command Type 'Set the CommandText to the proc name cmd.CommandText = m_strProcName 'Call refresh to retrieve the values cmd.Parameters.Refresh %> <% Dim blnTR1 Dim param for each param in cmd.Parameters If blnTR1 Then Response.Write "" Else Response.Write "" End If blnTR1 = NOT blnTR1 Response.Write "" & _ "" & _ "" & _ "" & _ "" & _ "" & _ "" & _ "" next %>

Proc Name = <%=m_strProcName%>
Parameter Name	Direction	Type	Precision	Size	Value
" & param.Name & "	" & GetParameterDirectionEnum(param.Direction) & _ " (" & param.Direction & ")	" & GetDataTypeEnum(param.Type) & _ " (" & param.Type & ")	" & param.Precision & "	" & param.Size & "	" & param.Value & "
<% Dim blnFirstParameter 'Is this is the first parameter Dim strDeclaration 'Function declaration Dim strCommandParameters 'Parameters for the command Dim strOutputParameters 'Retrieving of output parameters DIm strPrecisionParameters 'Setting of precision for Decimal and Numeric Dim strTempParamVarName 'The variable name for the parameter 'Default the Function name to the proc name strDeclaration = "Function " & m_strProcName & "(" blnFirstParameter = True If m_blnRecordset = True Then strDeclaration = strDeclaration & "rst" blnFirstParameter = False End If for each param in cmd.Parameters If Left(param.Name,1) = "@" Then strTempParamVarName = Mid(param.Name,2) Else strTempParamVarName = param.name End If If NOT param.Direction = 4 Then If NOT blnFirstParameter = True Then strDeclaration = strDeclaration & ", " Else blnFirstParameter = False End If strDeclaration = strDeclaration & strTempParamVarName If param.Direction = 3 Then strOutputParameters = strOutputParameters & strTempParamVarName & _ " = cmd.Parameters(""" & param.name & """).Value" & vbCrLf End If End If strCommandParameters = strCommandParameters & _ "cmd.Parameters.Append cmd.CreateParameter(""" & param.Name _ & """, " & param.Type & ", " & param.Direction & _ ", " & param.Size & ", " & strTempParamVarName & ")" & vbCrLf If param.type = 14 or param.type = 131 Then strPrecisionParameters = strPrecisionParameters & "cmd.Parameters(""" & _ param.name & """).Precision = " & param.Precision & vbCrLf End If next strDeclaration = strDeclaration & ")" Response.Write strDeclaration & vbCrLf & vbCrLf Response.Write "Dim cmd 'Command Object" & vbCrLf Response.Write "Dim RETURN_VALUE 'Return Value" & vbCrLf Response.Write vbCrLf Response.Write "RETURN_VALUE = Null" & vbCrLf Response.Write "Set cmd = Server.CreateObject(""ADODB.Command"")" & vbCrLf If m_blnRecordset = True Then Response.Write "Set rst = Server.CreateObject(""ADODB.Recordset"")" & vbCrLf End If Response.Write "cmd.ActiveConnection = """ & m_strConnectionString & """" & vbCrLf Response.Write "cmd.CommandType = 4 'Stored Procedure" & vbCrLf Response.Write "cmd.CommandText = """ & m_strProcName & """" & vbCrLf & vbCrLf Response.Write strCommandParameters Response.Write vbCrLf & strPrecisionParameters & vbCrLf & vbCrLf If m_blnRecordset = True Then Response.Write "rst.CursorLocation = 3 'adUseClient" & vbCrLf Response.Write "rst.Open cmd, , 3, 1 'adOpenStatic, adLockReadOnly" & vbCrLf & vbCrLf Response.Write "Set rst.ActiveConnection = Nothing" & vbCrLf Else Response.Write "cmd.Execute" & vbCrLf & vbCrLf End If Response.Write strOutputParameters Response.Write m_strProcName & " = cmd.Parameters(""RETURN_VALUE"").Value" & _ vbCrLf & vbCrLf Response.Write "Set cmd = Nothing" & vbCrLf & vbCrLf Response.Write "End Function" & vbCrLf %>

<% Set cmd = Nothing End Sub Function GetParameterDirectionEnum(lngDirection) Select Case lngDirection Case 0 'adParamUnknown GetParameterDirectionEnum = "adParamUnknown" Case 1 'adParamInput GetParameterDirectionEnum = "adParamInput" Case 2 'adParamOutput GetParameterDirectionEnum = "adParamOutput" Case 3 'adParamInputOutput GetParameterDirectionEnum = "adParamInputOutput" Case 4 'adParamReturnValue GetParameterDirectionEnum = "adParamReturnValue" Case Else GetParameterDirectionEnum = "Direction Not Found" End Select End Function Function GetDataTypeEnum(lngType) Select Case lngType Case 0 GetDataTypeEnum = "adEmpty" Case 2 GetDataTypeEnum = "adSmallInt" Case 3 GetDataTypeEnum = "adInteger" Case 4 GetDataTypeEnum = "adSingle" Case 5 GetDataTypeEnum = "adDouble" Case 6 GetDataTypeEnum = "adCurrency" Case 7 GetDataTypeEnum = "adDate" Case 8 GetDataTypeEnum = "adBSTR" Case 9 GetDataTypeEnum = "adIDispatch" Case 10 GetDataTypeEnum = "adError" Case 11 GetDataTypeEnum = "adBoolean" Case 12 GetDataTypeEnum = "adVariant" Case 13 GetDataTypeEnum = "adIUnknown" Case 14 GetDataTypeEnum = "adDecimal" Case 16 GetDataTypeEnum = "adTinyInt" Case 17 GetDataTypeEnum = "adUnsignedTinyInt" Case 18 GetDataTypeEnum = "adUnsignedSmallInt" Case 19 GetDataTypeEnum = "adUnsignedInt" Case 20 GetDataTypeEnum = "adBigInt" Case 21 GetDataTypeEnum = "adUnsignedBigInt" Case 64 GetDataTypeEnum = "adFileTime" Case 72 GetDataTypeEnum = "adGUID" Case 128 GetDataTypeEnum = "adBinary" Case 129 GetDataTypeEnum = "adChar" Case 130 GetDataTypeEnum = "adWChar" Case 131 GetDataTypeEnum = "adNumeric" Case 132 GetDataTypeEnum = "adUserDefined" Case 133 GetDataTypeEnum = "adDBDate" Case 134 GetDataTypeEnum = "adDBTime" Case 135 GetDataTypeEnum = "adDBTimeStamp" Case 136 GetDataTypeEnum = "adChapter" Case 138 GetDataTypeEnum = "adPropVariant" Case 139 GetDataTypeEnum = "adVarNumeric" Case 200 GetDataTypeEnum = "adVarChar" Case 201 GetDataTypeEnum = "adLongVarChar" Case 202 GetDataTypeEnum = "adVarWChar" Case 203 GetDataTypeEnum = "adLongVarWChar" Case 204 GetDataTypeEnum = "adVarBinary" Case 205 GetDataTypeEnum = "adLongVarBinary" Case 8192 GetDataTypeEnum = "adArray" Case Else GetDataTypeEnum = "Type Constant Not Found" End Select End Function %>