5.7 Editing Stored Procedures and Functions

To create a new stored procedure, right-click the Stored Procedures node under the connection node in Server Explorer. From the node's context menu, choose the Create Routine command. This command opens the SQL Editor.

Figure 5.14 Edit Stored Procedure SQL

To create a new stored function, right-click the Functions node under the connection node in Server Explorer. From the node's context menu, choose the Create Routine command.

To modify an existing stored routine (procedure or function), double-click the node of the routine to modify, or right-click this node and choose the Alter Routine command from the context menu. Either of the commands opens the SQL Editor.

Routine properties can be viewed in the Properties window. These properties are:

• Body

• Catalog

• Comment

• Creation Time

• Data Access

• Definer

• Definition

• External Name

• External Language

• Is Deterministic

• Last Modified

• Name

• Parameter Style

• Returns

• Schema

• Security Type

• Specific Name

• SQL Mode

• SQL Path

• Type

Some of these properties can have arbitrary text values, others accept values from a predefined set. In both cases, these values cannot be set from the properties panel.

You can also set all the options directly in the SQL Editor, using the standard CREATE PROCEDURE or CREATE FUNCTION statement.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control + S.

Figure 5.15 Stored Procedure SQL Saved

To observe the runtime behavior of a stored routine and debug any problems, use the Stored Procedure Debugger. For additional information, see Chapter 11, Debugging Stored Procedures and Functions.