SQLSourceSafe

Working with Stored Procedures

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

Adding a New Stored Procedure

To add a new stored procedure to the SQL Server database, you can:

Select Database\Add New Stored Procedure from the main menu; or
Click on the Add New Stored Procedure button on the tool bar; or
Right click on the Stored Procedures category node on the SQL Server Pane, then select Add New Stored Procedure from the context menu.

The Enter Stored Procedure Name dialog box appears (see Figure 1). Enter the name of the stored procedure in the dialog box and click on the OK button. The Stored Procedure Edit dialog box appears (see Figure 2 and Figure 3). A default stored procedure skeleton script is displayed in the Edit Procedure tab. Enter your script there. Click on the Check Syntax button to verify the syntax of your script. If there is no syntax error, you can click on the Save button to create the stored procedure in SQL Server database. Otherwise, you will need to fix the syntax error before saving it in SQL Server - per the SQL Server rule, you may not save a stored procedure with syntax errors in it.

Figure 1  Enter Stored Procedure Name dialog

Editing a Stored Procedure

To edit a stored procedure, you can:

Right click on the stored procedure item on the SQL Server Pane, then select Properties\Edit from the context menu; or
Double click on the stored procedure item on the SQL Server Pane; or
Highlight the stored procedure item on the SQL Server Pane, then select Database\Edit from the main menu

The Stored Procedure Edit dialog box appears (see Figure 2 and 3). You can edit the script of the stored procedure on Edit Procedure tab. When you finish the editing, click on the Check Syntax button to verify the syntax of the script. Click on the Save button to save the changes in SQL Server if there is syntax error. Otherwise, fix the syntax error and then save it - per the SQL Server rule, you may not save a stored procedure with syntax errors in it.

You can also test run the stored procedure on the Execute Procedure tab. To do that, highlight each parameter on the parameters list and enter the value for each parameter. Then, click on the Execute button to execute the stored procedure. If the stored procedure has some returns, they will be displayed in the Grids tab. If there is an error in executing the stored procedure, the message will be display in the Message tab.

Figure 2  Edit Stored Procedure Dialog – Execute Procedure Tab

Figure 3 Edit Stored Procedure Dialog – Edit Procedure Tab

Deleting a Stored Procedure

To delete a stored procedure, you can:

Right click on the stored procedure item on the SQL Server Pane, then select Delete from the context menu; or
Highlight the stored procedure item on the SQL Server Pane, then select Database\Delete… from the main menu.

Renaming a Stored Procedure

This operation allows you to rename a Stored Procedure.

Right click on the stored procedure item that you want to rename in the SQL Server Pane. Then Select Rename from the context menu; or
Highlight the stored procedure item you want to rename in the SQL Server Pane, then select Database\Rename from the main menu.