Adaptive Server stored procedure example

This example shows how to create an ASE stored procedure that connects to TRS and uses a parameter to choose the DB2 UDB system to use:

create proc dbp1
              @salary int

            as
              if @salary < 60000
                   exec BLUETRS...MIAMIrpc "select * from payroll"
            else
                   exec BLUETRS...DALLASrpc "select * from payroll"

In this example, the Adaptive Server is configured to connect to the TRS named “BLUETRS” and to execute an RPC. The value of the @salary parameter determines the language RPC that “BLUETRS” uses to route the select statement. Based on the value of @salary, one of the following occurs:

• If the value of @salary is less than 60,000, the procedure sends the select * from payroll statement to the TRS named “BLUETRS.” In “BLUETRS,” the AMD2 transaction in MIAMI executes it. The AMD2 transaction in MIAMI is mapped to the RPC named “MIAMIrpc” on “BLUETRS,” or

• If the value of @salary is greater than or equal to 60,000, the procedure executes against the AMD2 transaction in DALLAS, which is mapped to the RPC named “DALLASrpc” on “BLUETRS.”