Filter
Top Products
192 Microsoft 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.