Operation Manual

681
Building applications visually
Last updated 11/30/2015
About stored procedures
Although you can use server behaviors to build pages that modify databases, you can also use database manipulation
objects such as stored procedures or ASP command objects to build the pages.
A stored procedure is a reusable database item that performs some operation on the database. A stored procedure
contains SQL code that can, among other things, insert, update, or delete records. Stored procedures can also alter the
structure of the database itself. For example, you can use a stored procedure to add a table column or even delete a table.
A stored procedure can also call another stored procedure, as well as accept input parameters and return multiple values
to the calling procedure in the form of output parameters.
A stored procedure is reusable in the sense that you can reuse a single compiled version of the procedure to execute a
database operation a number of times. If you know a database task will be executed more than a few times—or the same
task will be executed by different applications—using a stored procedure to execute that task can make database
operations more efficient.
Note: MySQL and Microsoft Access databases do not support stored procedures.
Add a stored procedure (ColdFusion) (CS6)
You can use a stored procedure to modify a database. A stored procedure is a reusable database item that performs some
operation on the database.
Before you use a stored procedure to modify a database, make sure the stored procedure contains SQL that modifies
the database in some way. To create and store one in your database, consult your database documentation and a good
Transact-SQL manual.
1 In Dreamweaver, open the page that will run the stored procedure.
2 In the Bindings panel (Window > Bindings), click the Plus (+) button, and then select Stored Procedure.
3 In the Data Source pop-up menu, select a connection to the database containing the stored procedure.
4 Enter the ColdFusion Data Source user name and password.
5 Select a stored procedure from the Procedures pop-up menu.
Dreamweaver automatically fills in any parameters.
6 Select a parameter, and click Edit if you have to make changes.
The Edit Stored Procedure Variable dialog box appears. The name of the variable you are editing appears in the
Name box.
Note: You must enter test values for any stored procedure input parameters.
7 Make changes as necessary:
• Select a Direction from the pop-up menu. A stored procedure might have input values, output values, or both
input and output values.
• Select a SQL type from the pop-up menu. Enter a return variable, a run-time value, and a test value.
8 If the stored procedure takes a parameter, click the Plus (+) button to add a page parameter.
Note: You must enter corresponding page parameters for each stored procedure parameter return value. Do not add
page parameters unless there is a corresponding return value.
Click the Plus (+) button again to add another page parameter, if necessary.