### SQL stored procedures

#### Selecting an SQL stored procedure and changing parameters

1. Choose `Options` from the `File` menu.

   The Options dialog box appears.

2. Click the `Database` tab and make sure either the `Stored Procedures` or the `Reprompt user when connecting` check box is selected.
   - Selecting Stored Procedures automatically displays any available stored procedures when you log on to an SQL database.
   - Selecting Reprompt user when connecting displays the Allow Reporting On dialog box every time you select an ODBC data source. This dialog box provides the same options as the Explorer Options section on the Database tab of the Options dialog box, but provides those options for every ODBC data source you log on to.
3. In addition, you can specify `Table name LIKE` and `Owner LIKE` options if you wish.
   - Table name LIKE is based on the SQL LIKE clause. This option allows you to specify the kinds of table names you want to appear in the Choose SQL Table dialog box. You can use the underscore character (_) or the percent sign character (%) as wildcards with this function. The underscore character specifies any single character, while the percent sign signifies any character string. For example, DAV_ matches DAVE only, while DAV% matches DAVE and DAVID. Table name LIKE C% would display only those tables that have a table name beginning with the letter C.
   - Owner LIKE is also based on the SQL LIKE clause. The Owner LIKE option allows you to select the Owner (or Creator or Alias) of the table, not the table name itself. For example Owner LIKE C% would display only those tables that have an owner beginning with the letter C.

     Note: For more information on the SQL LIKE clause, refer to your SQL documentation.

4. Click `OK` to exit the Options dialog box.
5. Create a new report based on the SQL database that contains the stored procedure you want to use.
6. Double-click the ODBC data source for your SQL Server from the Data Explorer dialog box.

   The SQL Server Login dialog box appears.

7. Enter the required information to log in.
8. Click `Options`.

   The Options section of the SQL Server Login dialog box appears.

9. Verify the settings for your SQL Server.
10. Click `OK`.

    If you toggled on the Reprompt user when connecting check box in the Options dialog box, the Allow Reporting On dialog box now appears. Otherwise, skip to Step 12.

11. Make sure the `Procedures` check box is selected and click `OK` when finished.
12. Highlight an SQL stored procedure and click `Add`.

    The Enter Parameter Values dialog box appears.

    Note: Under the native driver for MS SQL Server, stored procedure input parameters are limited to 64 characters, rather than 255.

13. Highlight a parameter in the `Parameter Fields` list.

    Set to NULL Value is selected by default. If you want to assign a value, clear the Set to NULL Value check box.

14. Assign a value by typing into the `Discrete Value` box and then click `OK`.
15. Repeat Steps 13 and 14 for each parameter that appears in the `Parameter Fields` list.
16. On the Data Explorer dialog box, click `Close`.

    You can change parameter values at any time by choosing Stored Procedure Parameters from the Database menu.

17. Create your report using the fields from the stored procedure. Only the records that satisfy the parameter values you specified in the Enter Parameter Values dialog box are used in your report.

Seagate Software, Inc. http://www.seagatesoftware.com Please send comments to: techpubs@seagatesoftware.com