Deborah's Developer MindScape

The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to work with scripts for an existing database or build new scripts directly within Visual Studio. This post details how to add stored procedure scripts to a Database project

Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then follow the steps below to add stored procedure scripts to your project.

1. Right-click on the Stored Procedures node for the Database project in Solution Explorer:

2. Select Add | Stored Procedure

This opens the Add New Item dialog:

3. Enter the name of the new stored procedure and click the Add button.

The stored procedure template then appears in the code editor and you can enter the stored procedure information.

One of the really nice new features in Visual Studio 2010 is full Intellisense when building the stored procedures:

NOTE: To get full Intellisense on the available table fields, add the From clause first. Without the From clause, Visual Studio does not know how to resolve the field list and provide appropriate Intellisense.

NOTE: Intellisense would sometimes stop working for no reason I could figure out. Closing the script and reopening it often helped get Intellisense working again.

When you are ready to apply the stored procedure script to your development database, you can deploy the script as detailed in this post.

But if you want to simply validate the stored procedure syntax before deployment, use the Validate SQL Syntax icon in the toolbar OR right-click on the code editor and select Validate SQL Syntax. In either case, you will be asked to connect to your database. The syntax is then checked and any errors are defined in the Messages tab at the bottom of the code window.

You can also execute the script, writing the stored procedure to the database, directly from the code editor. Select the Execute SQL icon in the toolbar OR right-click on the code editor and select Execute SQL. You will be asked to connect to your database before the script is executed and the stored procedure is created in the defined database.

NOTE: Because the stored procedure scripts in the Database project are always Create Procedure scripts, you can not execute the script again once the stored procedure is created. The work around for this is to change "Create Procedure" to "Alter Procedure" in the script, execute the script, and change the script back to "Create Procedure".

You can also execute any SQL statements within the script by selecting the statements. Then right-click and select Execute SQL. The script is then executed and the results are displayed in the Results tab.

Use the techniques detailed in this post any time you want to add a stored procedure script to your Database project or work with a stored procedure script in your project.

Enjoy!