Calling a Stored Procedure

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The following example uses the modCallSP function to call a stored procedure in workflow script.

You can add the modCallSP function to workflow script if you want to create a Web-based user interface for executing row-level permissions. To use this function to execute row-level permissions, you must remove the logger.printstring function call. In addition, instead of using ActiveConnection, create your connection object. These permissions can be granted through the use of several stored procedures.

To use this example, add the "Calling the Function" code to the appropriate script procedure and add the "Example Script" code wherever you usually store functions.

Calling the Function

'// modCallSP
'//
'// --------------------------------------------------------------
    dim ret
    dim paramlist(3)
    paramlist(1) = "IssuesWorkflow"
    paramlist(2) =  1
    paramlist(3) = "domain\useralias"
    ret =  modCallSP("modGetExecutePermissions", True, 3, paramlist)
    call logger.printstring("permission check return value: " & ret & chr(13) & chr(10))

'// --------------------------------------------------------------

Example Script

'// --------------------------------------------------------------
'// Name      : modCallSP
'// Purpose   : calls a stored procedure
'//
'// Prereq    : none
'// Inputs    : strSP - name of the stored procedure to call
'//           : fReturnValue - do you want the return value from the call
'//           : numParams - number of parameters to set for the call
'//           : paramlist - single dimension array that has the parameter values starting at (1)
'//
'// Return    : returns -1 for failed calls, otherwise returns the value
'// --------------------------------------------------------------
Function modCallSP(strSP, fReturnValue, numParams, paramlist)
    
    '// assume failure
    modCallSP = -1
    
    '// declaration
    Dim numCount
    Dim strCommandText
    Dim objCommand
    
    '// initialization
    Set objCommand = CreateObject("ADODB.COMMAND")
    Set objCommand.ActiveConnection = Session.AppConnection
    
    '// put the command string together
    If fReturnValue = True Then
        strCommandText = "{? =call " & strSP & "("
    Else
        strCommandText = "{call " & strSP & "("
    End If
    If numParams > 0 Then
        strCommandText = strCommandText & "?"
    End If
    For numCount = 2 To numParams
        strCommandText = strCommandText & ",?"
    Next
    strCommandText = strCommandText & ")}"
    
    '// something like "{? =call modGetExecutePermissions(?,?,?)}"
    objCommand.CommandText = strCommandText

    '// fill the parameters list
    For numCount = 1 To numParams
        objCommand.Parameters(numCount) = paramlist(numCount)
    Next
    
    '// execute
    objCommand.Execute

    '// set return value
    If fReturnValue = True Then
        modCallSP = objCommand.Parameters(0)
    Else
        modCallSP = 0
    End If
    
    '// destroy object
    Set objCommand = Nothing

End Function

See Also

Script Examples for SQL Server | Finding a User's E-Mail Address in the User Directory | Sending Mail Using SMTP | Finding a Manager in the User Directory | Sending Mail to a Manager | Row-Level Permissions and the Workflow User | Stored Procedures