Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

May 3, 2010

VS 2010 Database Project: Adding Stored Procedures

Filed under: C#,VB.NET,Visual Studio @ 1:37 am

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:

image

2. Select Add | Stored Procedure

This opens the Add New Item dialog:

image

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.

image

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

image

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.

image

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!

3 Comments

  1.   limo — February 3, 2011 @ 6:30 am    Reply

    Hi, great blog!

    Previously I used to include “GRANT EXECUTE…” for the stored procedure in the create/alter script. Where do I set this when creating procedures in the new vs2010 database project?

  2.   NEHA — November 13, 2011 @ 9:11 am    Reply

    send those project list which have database is stored procedure ….

  3.   Mike — November 7, 2012 @ 12:00 pm    Reply

    Quote:
    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”.

    I’ve stumbled into this, and one thing to note: if you leave it as Alter, then Schema Compare won’t work.

    You have to remember to change it back to create. THAT SUCKS! Has anyone found a way around this? Other than put a drop statement on top of every single stored proc?

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2021 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs