Developing Stored Procedures
A stored procedure is a SQL statement (or series of statements) stored in a database and compiled. With SQL Server, stored procedures consist of Transact-SQL (T-SQL) code and have the capability to involve many coding constructs not typically found in ad hoc queries. For instance, you can implement error-handling routines within a stored procedure and even call into operating-system functions with so-called extended stored procedures.
For a given database, right-click the stored procedures folder in Server Explorer and select Add New Stored Procedure. A template for a stored procedure will open in the query designer. The SQL Editor is a close sibling to Visual Studio’s Code Editor; it includes support for IntelliSense, syntax coloring, breakpoints, and the more general text-editing features (cut-copy-paste, word wrapping, and so on).
Tip
Remember: if you don’t see the Stored Procedures folder within Server Explorer, you are likely not connecting to the data source using the native SQL Server provider. If that is the case, just add another connection to the database using the SQL Server provider.
Figure 13.17 shows the beginnings of a stored procedure in the SQL Editor window.
With the template loaded into the SQL Editor, writing a stored procedure involves typing in the lines of code and SQL that perform the required actions.
The capability to create and edit stored procedures is supported only in Microsoft SQL Server. You cannot use the Visual Studio tools to create a procedure in, say, an Oracle database.
Debugging Stored Procedures
In addition to coding stored procedures, you can leverage Visual Studio to help you debug them. With the stored procedure open in the SQL Editor window, set a breakpoint in the procedure by clicking in the Indicator Margin. (For more details on the indicator margin and general editor properties, see Chapter 6, “Introducing the Editors and Designers.”) With a breakpoint in place, right-click the stored procedure’s name in the Server Explorer tree and select Execute (see Figure 13.18).
The SQL Debugger is also parameter friendly. If the stored procedure uses any parameters, the debugger shows a dialog box to capture values for the parameters (see Figure 13.19).
You can quickly cycle through the list of parameters, supplying appropriate values. After you click OK, the stored procedure is executed. If you have set a breakpoint, execution pauses on the breakpoint. (A yellow arrow indicates the current line of execution within the editor, just the same as with the code editor window.) With execution stopped, you can use the Locals and Watch windows to debug the procedure’s code. See Chapter 10, “Debugging Code,” for a more thorough treatment of the Locals and Watch windows as debugging tools in Visual Studio.
The Debug menu is used to control execution and flow. If you select Continue, the procedure continues running up to the next breakpoint (if present).