Stored Procedures in the SQL Editor

Stored procedures enable you to manage your server-based database and display information about that database and its users. For example, you can use a stored procedure to display the title (from the titles table) and publisher (from the publishers table) for each author in the authors table.

Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, generate parameters, return single or multiple result sets, and return values.

You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:

You can execute a series of SQL statements in a single stored procedure.
You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.
The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements.
The compiled stored procedure can be cached in memory as well, for faster execution.

Creating New Stored Procedures

The SQL editor can be opened from the Data View window. The Data View window can be opened from the View menu or the Standard toolbar.

To create a new stored procedure

In Data View, right-click the Stored Procedures folder or any stored procedure in that folder.
Choose New Stored Procedure from the shortcut menu.
A new stored procedure is created using a template containing SQL statements.
Replace StoredProcedure in the first line with the name of the procedure. For example, you might use "MyProcedure" as the name:
```
Create Procedure MyProcedure
```
Note Stored procedures must have unique names. If you choose a name that is already assigned to another stored procedure in your project, an error message is displayed.
Write the remaining procedure text in SQL.

For more information and examples of stored procedures, see the documentation for your server. If you are using Microsoft^® SQL Server™, see CREATE PROCEDURE statement in the SQL Server documentation. The Visual Basic SQL Editor also works with Oracle Stored Procedures, Functions, and Packages.

Running Stored Procedures

You can run a stored procedure against your database to execute the SQL statements it contains and display the results in the Immediate window.

To run a stored procedure

In Data View, expand the Stored Procedures folder.
Right-click the name of the stored procedure that you want to run. Choose Run from the shortcut menu.
If any parameters are required, a dialog appears to enter the parameter values.

Copying Stored Procedures

You can copy a stored procedure as the first step in creating a new stored procedure for your database. Because stored procedures must have names that are unique, the new stored procedure is automatically assigned a new unique name based on the original name.

To copy a stored procedure

In Data View, expand the Stored Procedures folder.
Right-click the name of the stored procedure that you want to copy. Choose Design from the shortcut menu.
The SQL Editor opens with the stored procedure.
Select all the text in the SQL Editor.
Right-click the SQL Editor. Choose Copy from the shortcut menu.
Right-click the SQL Editor. Choose New Stored Procedure Wizard from the shortcut menu.
Clear the template text.
Right-click the SQL Editor. Choose Paste from the shortcut menu to create your new stored procedure.
Change the name of the stored procedure.
Save the stored procedure to the database.

You can edit the SQL statements in the new stored procedure.

Setting Execution Permissions on Stored Procedures

You can set execute permissions for a stored procedure that you own to allow access to the stored procedure by specific users or groups. In many databases, such as Microsoft^® SQL Server™ and Oracle Database Server, if you are not the database owner, then you must explicitly grant permissions for your stored procedure to other users. Use SQL Server’s ISQL utility, Enterprise Manager, or an Oracle Tool to run the change commands or visually set the permissions.