Microsoft 9GD00001 manual Adding Stored Procedures, Listing 7-1 Stored procedure example

192Microsoft Visual Studio 2010: A Beginner’s Guide

copy of the diagram, right-click and copy to clipboard or select File Print. You can also add multiple diagrams to the Database Diagrams folder, allowing you to have multiple different views for your convenience.

In addition to tables and diagrams, you can add database views, stored procedures, functions, synonyms, types, and assemblies. Most of these database items are for advanced scenarios, but it’s important that you know about stored procedures, which are covered next.

Adding Stored Procedures

A stored procedure is code that is written in SQL and saved as part of a database. It is a method stored in the database itself, and not in your program code; hence the term stored procedure. In this section, I’ll show you how to create and execute a stored procedure. Later sections of this chapter will show you how to execute this stored procedure, which runs a data query, through LINQ to SQL.

To create a stored procedure, right-click the Stored Procedure folder for the database in Server Explorer and select Add New Stored Procedure. You’ll see an editor appear with skeleton code for a stored procedure. Modify the code so that it retrieves all of the data from the Customer table, as shown in Listing 7-1. After modifying the template code, click Save and you’ll see the stored procedure appear in the Stored Procedures folder of the database in Server Explorer.

Listing 7-1 Stored procedure example

CREATE PROCEDURE GetCustomers

AS

declare @cust_count int

select @cust_count = count(*) from Customer if @cust_count > 0

begin

select [Name] from Customer

end return

Listing 7-1 declares a variable named @cust_count and runs a select statement to assign the number of customers, count(*), to @cust_count. If @cust_count is larger than 0, there are customers and the stored procedure queries for customer names. Teaching TSQL (Microsoft’s dialect of SQL) syntax is outside the scope of this book, but you can download SQL Server Books Online for free and purchase McGraw-Hill’s Microsoft SQL Server 2008: A Beginner’s Guide, Fourth Edition by Dusan Petkovic (McGraw-Hill/Professional, 2008) to get started.