Solved

Obtaining SQL Server Stored Procedure Parameters

Posted on 2008-10-29
3
289 Views
Last Modified: 2013-11-11
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

Open in new window

0
Comment
Question by:Howard Bash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 22839130
HOW TO: Retrieve information of Stored Procedure in SQL Server
http://weblogs.asp.net/Varad/archive/2004/08/21/218468.aspx
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22839132
You use dervieparamter like here
public void fillSPParameters(String procedureName)
{
try
{
_command.CommandText = procedureName;
_command.CommandType = CommandType.StoredProcedure;
openConnection();
SqlCommandBuilder.DeriveParameters(_command);
}

                 }
0
 
LVL 42

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 22839262
also check the article:
SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials

http://www.davidhayden.com/blog/dave/archive/2006/11/01/SqlCommandBuilderDeriveParameters.aspx
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question