Macromedia Dreamweaver UltraDev - Creating pages that use stored procedures: Creating an ASP page that uses a stored procedure

Home > Products > UltraDev > Support > Defining Dynamic Content

Creating an ASP page that uses a stored procedure

This section describes how to use UltraDev to create an ASP page that calls a stored procedure in your database. Before you begin, make sure your database contains a stored procedure. To create and store one in your database, consult your database documentation and a good Transact-SQL manual.

To create an ASP page that calls a stored procedure:

1	In UltraDev, open the page that will run the stored procedure. .
2	Open the Server Behaviors panel (Window > Server Behaviors), click the plus (+) button, and choose Command.
	The Command dialog box appears.
3	Enter a name for the command, choose a connection to the database containing the stored procedure, and choose Stored Procedure from the Type pop-up menu.
4	If the stored procedure returns a recordset, select the Return Recordset option and enter a name for the recordset.
5	In the Database Items box, expand the Stored Procedures branch, select the stored procedure from the list, and click the Procedure button.
	UltraDev automatically fills in the SQL and Variables boxes, as follows:

6	If the stored procedure returns a recordset, test it if you want by entering a value in the Default Value column in the Variables box and clicking Test.
	UltraDev runs the stored procedure and displays the recordset, if any. In the above example, passing a default value of 100 to the stored procedure generates the following recordset:

After you close the dialog box, UltraDev inserts ASP code in your page that, when run on the server, creates a command object that runs a stored procedure in the database. The stored procedure in turn performs a database operation, such as generating a recordset or inserting a record.

By default, the code sets the Prepared property of the Command object to True, which makes the application server reuse a single compiled version of the object every time the stored procedure is run. If you know the command will be executed more than a few times, having a single compiled version of the object can improve the efficiency of database operations. However, if the command will only be executed one or two times, using one might actually slow down your Web application because the system has to pause to compile the command. To change the setting, switch to Code view and change the Prepared property to False.

Note: Not all database providers support prepared commands. If your database does not support it, you might get an error message when you run the page. Switch to Code view and change the Prepared property to False. .

If the stored procedure generates a recordset, the procedure will appear in UltraDev in both the Server Behaviors panel and the Data Bindings panel (Window > Data Bindings). Use the Data Bindings panel to display the recordset's content on your page by selecting a recordset column in the panel and dragging it to the page. UltraDev inserts code in the page that, when run on the server, displays the content of the column of one record.

If the stored procedure takes parameters, you might create a page that gathers the parameter values and submits them to the page with the stored procedure. For example, you may create a page that uses URL parameters or an HTML form to gather parameter values from users.