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 …

image

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

image

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

CREATE PROCEDURE [dbo].[CustomerInfoRetrieve]

AS

       SELECT

              LastName,

              FirstName

       FROM

              CustomerInfo

       ORDER BY

              LastName

RETURN 0

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.

image

Notice also that it validates your syntax as you go:

image

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

Visual Studio presents a preview dialog:

image

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.

image

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

Enjoy!

RSS feed for comments on this post. TrackBack URI

Leave a comment

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