Deborah's Developer MindScape

         Tips and Techniques for Web and .NET developers.

July 29, 2013

Adding Stored Procedures with SSDT

The SQL Server Object Explorer in Visual Studio 2012 is part of the SQL Server Data Tools (SSDT). You can use the SQL Server Object Explorer to design your database, tables, stored procedures, and other SQL Server objects. This post focuses on creating a stored procedure

See this link for an introduction to SQL Server Object Explorer.

1) Open the SQL Server Object Explorer toolbox using View | SQL Server Object Explorer from Visual Studio.

2) Drill down to your database, then the Programmability node, then to Stored Procedures.

3) Right-click and select Add New Stored Procedure …


4) Visual Studio creates a new stored procedure template and opens it in a code window.


You can then modify the template as needed for your stored procedure. This example is a simple retrieve:

CREATE PROCEDURE [dbo].[CustomerInfoRetrieve]







       ORDER BY



Notice that as you type, the editor provides Quick Info and statement completion. But not until you enter the From clause so it knows the table name.


Notice also that it validates your syntax as you go:


When you have finished entering your stored procedure, click the Update button.

Visual Studio presents a preview dialog:


Click Generate Script to create a deployment script containing the T-SQL. Click Update Database to directly update the database with the new table.

If you select Update Database, the results of the update are displayed in the Data Tools Operations window that, by default, appears on the bottom of the page. It provides links to view details such as detailed results.


Use the SQL Server Object Explorer any time you need to create or edit a stored procedure directly in your database.


RSS feed for comments on this post. TrackBack URI

Leave a comment

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