Calling a Stored Procedure

See Also

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. For more information, see Creating a Row-level Permissions User Interface. For reference information about stored procedures available in Microsoft Access Workflow Designer for Microsoft SQL Server™, see Stored Procedures.

To use this example, add the function call to the script procedure on the Shared Script tab in the Workflow Process pane. Then, add the following example code to the beginning or end of the text in the Shared Script tab.

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