Create and deploy an SQL stored procedure

In this exercise, you use a wizard to create a Db2® SQL stored procedure. The stored procedure uses an employee's department ID that the user enters to return employee information from the EMPLOYEE table. You use a second wizard to create the SQL statement that queries the database.

To create and deploy an SQL stored procedure:

1. In the Data Project Explorer view, expand SPDevelopment.
2. Right-click Stored Proceduresand select New > Stored Procedure.
   The New Stored Procedure wizard opens.
3. In the Name field, type SPEmployee.
4. In the Language field, select SQL.
5. Click Next.
6. On the SQL Statements page, click Create SQL.
   This action starts the New SQL Statement wizard that guides you through the creation of an SQL statement.

   Note: If the Create SQL option is not activated, verify that you have an active connection to the Db2 database. You can do check the connection by right-clicking the connection, and selecting Connect to connect, or Ping to ping the database. If the ping was successful, a dialog, Ping Successful!, opens.
7. On the first page of the New SQL Statement wizard, keep the defaults to create a SELECT statement.
8. Ensure that Be guided through creating a SQL statement is selected. Click Next.
9. In the Available Tables list, expand schemaname and select the schemaname.EMPLOYEE table.
10. Click > to move this table to the Selected Tables list.
11. Click the Columns tab at the top of the Construct an SQL Statement page of the wizard.
12. Expand the schemaname.EMPLOYEE tree, and select EMPNO, FIRSTNME, LASTNAME, and WORKDEPT.
13. Click > to move the columns to the Selected Columns list.
14. Click the Conditions tab.
15. Double-click in the first row in the Column column.
    A drop-down box opens.
16. Click the down arrow and select EMPLOYEE.WORKDEPT. The = operator is displayed automatically in the Operator column to the right.
17. In the Value column, type :dept and press Enter.
    This action creates a host variable, dept, that is used as a user input parameter for the stored procedure.
18. To see the SQL statement that you created, click Next .
    The SQL statement looks like the following example:

    SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT   FROM schemaname.EMPLOYEE   WHERE WORKDEPT = :dept

19. To close the New SQL Statement wizard, click Finish.
20. In the New Stored Procedure wizard, click Next.
21. On the Parameters page, in the SQL error handling code field, select SQLSTATE and SQLCODE.
    In the Parameters table, you see the input parameter, dept, that you created, and the output parameters for SQLSTATE and SQLCODE. You can create more user parameters on this page, but for the purposes of this tutorial, keep the parameters as they are.
22. Click Next.
23. On the Deploy Options page of the wizard, select the Deploy on Finish and Enable debugging check boxes.
    Note: Later in this tutorial, you can debug this stored procedure by using the integrated SQL stored procedure debugger included in the workbench. When you enable the deployment and debugging options in this wizard, you can later use the procedure debugger. If you did not select the deployment and enable debugging options here, you can manually deploy and enable the stored procedure for debugging later by right-clicking the stored procedure in the Data Project Explorer view, and selecting Deploy from the menu.
24. Click Next.
    On the Code Fragments page of the wizard, you can optionally specify files that contain code fragments to insert in the stored procedure body. In this case, do not specify code fragments.
25. Click Next and review your selections on the Summary page of the wizard.
    Tip: To preview the SQL for the new stored procedure before you create it, click Show SQL.
26. to create and deploy the stored procedure, click Finish.