Using a Stored Procedure as Record Source

You can use a stored procedure as a record source.

To use a stored procedure as a record source:

1. Add a graphics page using the Citect Graphics Builder and save it as Recipe.
2. Insert a Database Exchange control on the page.
3. Double-click the Database Exchange object to display the Properties dialog box.
4. Click the Connection tab.
5. Click Build to display the Data Link Properties form.
6. Select Microsoft OLE DB Provider for SQL Server and then click Next. Be aware that you can also use Microsoft OLE DB Provider for ODBC Drivers if an SQL DSN has been configured in ODBC settings.
7. Click the Connection tab and enter an SQL server name and user login, and then select Northwind as the database.
8. Click Test Connection to verify that the connection works and then click OK.
9. On the Database Exchange Properties form, select Stored Procedure as the command type and then select Employee Sales by Country;1 from the list as shown below. The parameters box prompts you to enter the number of parameters that the selected stored procedure has.
10. In the parameters box, type ('1996/07/01', '1996/07/21').

The resulting data looks like this:

Notes

11. Each parameter must be enclosed by ‘ ‘ and separated by a comma.
12. All parameters must be enclosed by ().
13. As a parameter is entered as a string, the ActiveX control tries to convert it to its original data type defined in the stored procedure. So for datetime type parameters, the parameter entry must follow the format ‘YYYY/MM/DD HH:mm:ss’.
14. Version 3.1.1 or later of Database Exchange control is required to support stored procedures having spaces in their names.