Using Stored Procedures

Areas: ASP, ASP.NET(C#), ColdFusion, Java Servlets, JSP, Perl, Perl, General 

When setting up the Data Source property of Grid and Record forms, the norm is to have a database table as the Data Source Type. However, situations arise whereby it would be more convenient to use a database stored procedure as the form data source. Configuring a form to use a stored procedure as the data source is not much different than configuring a table data source. Obviously, the database you are using must support stored procedures and the particular stored procedure to be used must already exist in the database.

To configure a grid form to use a stored procedure, click on the […] button of the Data Source property for the grid. In the Data Source window, set the Data Source Type field to Procedure. After doing this, the Procedure dropdown list will contain a list of the stored procedures available in the current connection. From this list, select the procedure you want to use.

After selecting the procedure, click on the Get Parameters button to retrieve any parameters defined for the procedure. In most cases, the parameters will be retrieved with their proper properties such as Direction and Data Type. You can double-click on any parameter and change its properties if the default ones are not correct. In the case of a grid form, the parameters are used to determine what records will be displayed in the grid so it is important that the Direction, Data Type, Type and Source properties be configured correctly. After configuring the properties, click Ok to close the Data Source window.

With the Data Source thus setup, you can then add fields to the form based on the fields returned by the procedure. Bear in mind that using a stored procedure has some limitations which don’t apply when a table is used instead. For instance, a grid cannot have a navigator when a stored procedure is used and neither does sorting of the form fields work. This is because the stored procedure is used ‘as-is’. On the contrary, when a table is used as the data source, SQL queries can be created programmatically to facilitate navigation and sorting.

A stored procedure can also be used to insert, update or delete records using a record form. You will notice that in the Properties window, a record form has the following properties:

  • Custom Insert Type
  • Custom Insert
  • Custom Update Type
  • Custom Update
  • Custom Delete Type
  • Custom Delete

If for instance you want to use a stored procedure to insert records, you would set the Custom Insert Type property to Procedure then use the Custom Insert property to configure the procedure in a similar manner as was done using the Data Source property. Similarly, the other 4 properties can be used to configure stored procedures to update and delete records respectively.

Note that a record form can still have its Data Source property configured to use an ordinary table and yet have procedures for the insert, update and delete operations. This is because the Data Source property is different from the 6 properties mentioned above. Essentially, the Data Source property determines the means by which data will be retrieved to appear in the form while the other six properties mentioned above determine the means by which the data is manipulated depending on the operation (Insert, update, delete).

Finally, stored procedures can also be used to populate ListBox and Radiolist controls. In this case, the List Source Type property is set to Procedure then the List Data Source property is used to configure the stored procedure and its parameters.

Viewed 34010 times.   Last updated: 7/31/2002 1:03:03 AM