Obtaining SQL Server Stored Procedure Parameters

I have been working on automating the process of analyzing stored procedures for either an Oracle database or an SQL Server database.   I found that by using a combination of ADO and parameter refresh,  I get the parameter collection back and can then build up  a nice report for my Oracle stored procedures.  However,  it does not appear to work when I attempt to obtain this same type of  information from an SQL Server database.

I will attach the working in Oracle and code.  Imagine that I have replaced all "Oracle" references with SQL.

Public Function GetStoredProcedureInputParams(ByVal sProcName As String) As System.Collections.Generic.List(Of ColumnRecord) 
 
        Dim oCmd As New ADODB.Command
        Dim strConn As String
        Dim oPar As ADODB.Parameter
        Dim lProcParams As List(Of ColumnRecord) = New List(Of ColumnRecord)
 
        Dim adoconn As ADODB.Connection = New ADODB.Connection
        strConn = "Provider=msdaora;" & Constants.HRDC_ConnString
        adoconn.ConnectionString = strConn
        adoconn.Open()
 
        oCmd.ActiveConnection = adoconn
        oCmd.CommandType = adCmdStoredProc
 
        oCmd.CommandText = sProcName
 
        'Retrieve information about parameters
        oCmd.Parameters.Refresh()
 
        For Each oPar In oCmd.Parameters
            Dim nc As New ColumnRecord
 
            nc.ColumnName = oPar.Name
            nc.ADOColumnType = oPar.Type
            nc.ColumnDirection = oPar.Direction
 
            'Set Parameter Direction "Friendly" form
            Select Case oPar.Direction
                Case adParamUnknown
                    nc.ColumnDirectionFriendly = "adParamUnknown"
                Case adParamInput
                    nc.ColumnDirectionFriendly = "adParamInput"
                Case adParamOutput
                    nc.ColumnDirectionFriendly = "adParamOutput"
                Case adParamInputOutput
                    nc.ColumnDirectionFriendly = "adParamInputOutput"
                Case adParamReturnValue
                    nc.ColumnDirectionFriendly = "adParamReturnValue"
            End Select
 
            'Get Parameter Type "Friendly" form
            Select Case oPar.Type
                Case adBigInt
                    nc.ColumnTypeFriendly = "adBigInt"
                Case adBinary
                    nc.ColumnTypeFriendly = "adBinary"
                Case adBoolean
                    nc.ColumnTypeFriendly = "adBoolean"
                Case adBSTR
                    nc.ColumnTypeFriendly = "adBSTR"
                Case adChapter
                    nc.ColumnTypeFriendly = "adChapter"
                Case adChar
                    nc.ColumnTypeFriendly = "adChar"
                Case adCurrency
                    nc.ColumnTypeFriendly = "adCurrency"
                Case adDate
                    nc.ColumnTypeFriendly = "adDate"
                Case adDBDate
                    nc.ColumnTypeFriendly = "adDBDate"
                Case adDBTime
                    nc.ColumnTypeFriendly = "adDBTime"
                Case adDBTimeStamp
                    nc.ColumnTypeFriendly = "adDBTimeStamp"
                Case adDecimal
                    nc.ColumnTypeFriendly = "adDecimal"
                Case adDouble
                    nc.ColumnTypeFriendly = "adDouble"
                Case adEmpty
                    nc.ColumnTypeFriendly = "adEmpty"
                Case adError
                    nc.ColumnTypeFriendly = "adError"
                Case adFileTime
                    nc.ColumnTypeFriendly = "adFileTime"
                Case adGUID
                    nc.ColumnTypeFriendly = "adGUID"
                Case adIDispatch
                    nc.ColumnTypeFriendly = "adIDispatch"
                Case adInteger
                    nc.ColumnTypeFriendly = "adInteger"
                Case adIUnknown
                    nc.ColumnTypeFriendly = "adIUnknown"
                Case adLongVarBinary
                    nc.ColumnTypeFriendly = "adLongVarBinary"
                Case adLongVarChar
                    nc.ColumnTypeFriendly = "adLongVarChar"
                Case adLongVarWChar
                    nc.ColumnTypeFriendly = "adLongVarWChar"
                Case adNumeric
                    nc.ColumnTypeFriendly = "adNumeric"
                Case adPropVariant
                    nc.ColumnTypeFriendly = "adPropVariant"
                Case adSingle
                    nc.ColumnTypeFriendly = "adSingle"
                Case adSmallInt
                    nc.ColumnTypeFriendly = "adSmallInt"
                Case adTinyInt
                    nc.ColumnTypeFriendly = "adTinyInt"
                Case adUnsignedBigInt
                    nc.ColumnTypeFriendly = "adUnsignedBigInt"
                Case adUnsignedInt
                    nc.ColumnTypeFriendly = "adUnsignedInt"
                Case adUnsignedSmallInt
                    nc.ColumnTypeFriendly = "adUnsignedSmallInt"
                Case adUnsignedTinyInt
                    nc.ColumnTypeFriendly = "adUnsignedTinyInt"
                Case adUserDefined
                    nc.ColumnTypeFriendly = "adUserDefined"
                Case adVarBinary
                    nc.ColumnTypeFriendly = "adVarBinary"
                Case adVarChar
                    nc.ColumnTypeFriendly = "adVarChar"
                Case adVariant
                    nc.ColumnTypeFriendly = "adVariant"
                Case adVarNumeric
                    nc.ColumnTypeFriendly = "adVarNumeric"
                Case adVarWChar
                    nc.ColumnTypeFriendly = "adVarWChar"
                Case adWChar
                    nc.ColumnTypeFriendly = "adWChar"
                Case Else
                    nc.ColumnTypeFriendly = "other"
            End Select
 
            lProcParams.Add(nc)
 
        Next oPar
 
        oCmd = Nothing
 
        'Returns a list of Column Records for the Stored Proc
        Return lProcParams
 
    End Function

Select allOpen in new window