Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Obtaining SQL Server Stored Procedure Parameters

Posted on 2008-10-29
3
Medium Priority
?
299 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 43

Accepted Solution

by:
Eugene Z earned 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 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