Databases › Query Database Operator › Run a Stored Procedure

Run a Stored Procedure

To run a stored procedure against a database, use the Query Database operator.

This example uses the SQL Server driver that CA Process Automation provides during installation. To run the process against another database, upload the corresponding JDBC driver from the Manage User Resources palette on the Configuration tab. CA Process Automation can access the driver when you restart the Orchestrator service. For more information, see the Content Administrator Guide.

You can duplicate this example in the SQL Server Management Studio in the PAMReporting database. PAMReporting is the database name that is provided for the CA Process Automation Reporting tables during the installation. You can use a different name. You can also use any database of your choice.

Follow these steps:

1. Create a stored procedure that contains the following body:

   USE PAMReporting
   GO
   -- ================================================
   -- Template generated from Template Explorer using:
   -- Create Procedure (New Menu).SQL
   --
   -- Use the Specify Values for Template Parameters 
   -- command (Ctrl-Shift-M) to fill in the parameter 
   -- values below.
   --
   -- This block of comments will not be included in
   -- the definition of the procedure.
   -- ================================================
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE PROCEDURE sp_getSOAPRows 
          -- Add the parameters for the stored procedure here
   AS
   BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          -- SET NOCOUNT ON;
   
       -- Insert statements for procedure here
             select count(*) from SOAPClientCall;
   END
   GO  
   

2. The procedure returns the number of rows in a table that was called as a SOAPClientCall.
3. Click Parse in the SQL Server Management Studio.
4. Click Execute in the SQL Server Management Studio.

   CA Process Automation saves the new stored procedure in the PAMReporting database.

5. Create a process with the Query Database operator, then enter the following line in the Inline text for the operator:

   EXECUTE sp_getSOAPRows
   

6. In the Database Server Login parameters, enter relevant details that CA Process Automation requires to communicate with your database.
7. Save the process.
8. Run the process.
9. Open the operator dataset.

   The number of rows the procedure returns is displayed as a result of the process.

   Note: Your result depends on the number of rows in the SOAPClientCall table.