SQL Statements...where?

[OfficeUser]

Are SQL statements typically contained in the same area in an database? If so, where might be a good place to look? Thanks.

 

[james_lankford]

this will tell you the parameters and types for a sql server stored procedure

just change
sp_who
to
sp_rpts_enzymes_qry

this works with SQL Server Express 2008 R2
you will of course have to change the user id, password, server name and database in the connection string

so start excel, start a macro in hte visual basic editor
then paste the following into the visual basic editor

you'll have to include a reference from the menu. If you don't have ActiveX 2.8 then an earlier version should do

I didn't fully type everything out because I got tired of typing
So the debug.print doesn't really print anything out, but if you step through the code you'll see exactly which line the "select case" prints on

Option Explicit

' from the VBA menu
' Tools / References
' Microsoft ActiveX Data Objects 2.8 Library
'***************************************************
'  
'***************************************************
Sub get_procedure_params()
    
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim param As ADODB.Parameter
    
    Set conn = New ADODB.Connection
    
    ' http://www.connectionstrings.com/
    conn.ConnectionString = "Provider=SQLNCLI10;Server=JAMESL-PC\SQLEXPRESS;Database=master;Uid=sa;Pwd=some_password;"
    
    conn.Open
        
    Set cmd = New ADODB.Command
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_who"
    
    Set cmd.ActiveConnection = conn
    
    cmd.Parameters.Refresh
    
    For Each param In cmd.Parameters
        Debug.Print "****************************************"
        Debug.Print "Param name = " & param.Name & vbCrLf & " Type = " & param.Type & " Param Direction = " & param.Direction
        Select Case param.Type
            Case ADODB.DataTypeEnum.adArray
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adBigInt
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adBinary
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adBoolean
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adBSTR
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adChapter
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adChar
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adCurrency
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adDate
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adDBDate
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adDBTime
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adDBTimeStamp
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adDecimal
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adDouble
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adEmpty
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adError
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adFileTime
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adGUID
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adIDispatch
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adInteger
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adIUnknown
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adLongVarBinary
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adLongVarChar
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adLongVarWChar
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adNumeric
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adPropVariant
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adSingle
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adSmallInt
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adTinyInt
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adUnsignedBigInt
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adUnsignedInt
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adUnsignedSmallInt
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adUnsignedTinyInt
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adUserDefined
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adVarBinary
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adVarChar
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adVariant
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adVarNumeric
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adVarWChar
                Debug.Print "Param Type = "
            Case ADODB.DataTypeEnum.adWChar
                Debug.Print "Param Type = "
            Case Else
                Debug.Print "Param Type = "
        End Select
        
        Select Case param.Direction
            Case ADODB.ParameterDirectionEnum.adParamInput
                Debug.Print "Param Direction = "
            Case ADODB.ParameterDirectionEnum.adParamInputOutput
                Debug.Print "Param Direction = "
            Case ADODB.ParameterDirectionEnum.adParamOutput
                Debug.Print "Param Direction = "
            Case ADODB.ParameterDirectionEnum.adParamReturnValue
                Debug.Print "Param Direction = "
            Case ADODB.ParameterDirectionEnum.adParamUnknown
                Debug.Print "Param Direction = "
            Case Else
                Debug.Print "Param Direction = "
        End Select
        Debug.Print "****************************************"
    Next
        
    conn.Close
    
    Set param = Nothing
    Set cmd = Nothing
    Set conn = Nothing
        
End Sub
'***************************************************

 

[Alex O]

Thanks for the information....this will be very helpful!

Alex