Walkthrough: Creating Stored Procedures for the Northwind Customers Table

Статья
02/04/2013

This walkthrough shows you how to create four stored procedures in the Northwind sample database. The stored procedures enable you to select, insert, update, and delete customers from the database.

The Next Steps section has links to topics that show you how to use these stored procedures.

Prerequisites

To complete this walkthrough, you need:

Access to the SQL Server version of the Northwind sample database. For more information, see How to: Install Sample Databases.

Connecting to the Northwind Database

Before you create the stored procedures, you have to connect to the SQL Server version of the Northwind database. The following procedure shows you how to create a data connection.

Note

If you already have a data connection to the Northwind database, you can go to the next section, Creating the Stored Procedures.

To create a data connection to the Northwind SQL Server database

On the View menu, click Server Explorer/Database Explorer.
Right-click Data Connections and then click Add Connection.
In the Choose Data Source dialog box, click Microsoft SQL Server, and then click OK.

If the Add Connection dialog box opens, and the Data source is not Microsoft SQL Server (SqlClient), click Change to open the Choose/Change Data Source dialog box, click Microsoft SQL Server, and then click OK. For more information, see Choose/Change Data Source Dialog Box.
Click a Server name in the drop-down list, or type the name of the server on which the Northwind database is located.
Based on the requirements of the database or application, either click Use Windows Authentication or use a specific user name and password to log on to the computer running SQL Server (SQL Server Authentication). For more information, see Add/Modify Connection (Microsoft SQL Server).
Click the Northwind database in the Select or enter a database name list.
Click OK.

The data connection is added to Server Explorer/Database Explorer.

Creating the Stored Procedures

Create the stored procedures by using the Visual Database Tools available in Server Explorer/Database Explorer.

To create the stored procedures by using a SQL script

Expand the Northwind database in Server Explorer/Database Explorer.
Define the SelectCustomers stored procedure by performing the following tasks:
1. Right-click the Stored Procedures node and then click Add New Stored Procedure.
2. Replace the CREATE PROCEDURE template following code:
```
CREATE PROCEDURE dbo.[SelectCustomers]
AS
    SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
```
3. Save the procedure.
4. The SelectCustomers stored procedure is added to the Northwind database. Notice that the procedure definition changes from CREATE PROCEDURE to ALTER PROCEDURE when you save it.

Define the InsertCustomers stored procedure by performing the following tasks:

Right-click the Stored Procedures node and then click Add New Stored Procedure.

Replace the CREATE PROCEDURE template with the following code:

CREATE PROCEDURE dbo.InsertCustomers
(
    @CustomerID nchar(5),
    @CompanyName nvarchar(40),
    @ContactName nvarchar(30),
    @ContactTitle nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @Phone nvarchar(24),
    @Fax nvarchar(24)
)
AS
    SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)

Save the procedure.
The InsertCustomers stored procedure is added to the Northwind database. Notice that the procedure definition changes from CREATE PROCEDURE to ALTER PROCEDURE when you save it.

Define the UpdateCustomers stored procedure by performing the following tasks:

Right-click the Stored Procedures node and then click Add New Stored Procedure.

Replace the CREATE PROCEDURE template with the following code:

CREATE PROCEDURE dbo.UpdateCustomers
(
    @CustomerID nchar(5),
    @CompanyName nvarchar(40),
    @ContactName nvarchar(30),
    @ContactTitle nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @Phone nvarchar(24),
    @Fax nvarchar(24),
    @Original_CustomerID nchar(5)
)
AS
    SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)

Save the procedure.
The UpdateCustomers stored procedure is added to the Northwind database. Notice that the procedure definition changes from CREATE PROCEDURE to ALTER PROCEDURE when you save it.

Define the DeleteCustomers stored procedure by performing the following tasks:
1. Right-click the Stored Procedures node and then click Add New Stored Procedure.
2. Replace the CREATE PROCEDURE template with the following code:
```
CREATE PROCEDURE dbo.DeleteCustomers
(
    @Original_CustomerID nchar(5)
)
AS
    SET NOCOUNT OFF;
DELETE FROM [dbo].[Customers] WHERE (([CustomerID] = @Original_CustomerID))
```
3. Save the procedure.
4. The DeleteCustomers stored procedure is added to the Northwind database. Notice that the procedure definition changes from CREATE PROCEDURE to ALTER PROCEDURE when you save it.

Next Steps

The following walkthroughs show you how to use the stored procedures that you just created:

How to: Assign Stored Procedures to Perform Updates, Inserts, and Deletes (O/R Designer)

Walkthrough: Creating LINQ to SQL Classes (O/R Designer)

Walkthrough: Customizing the Insert, Update, and Delete Behavior of Entity Classes

Note

If you want to repeat this walkthrough, you must first delete the stored procedures that you defined. Otherwise you will see the error: There is already an object named ‘ProcedureName’ in the database.

Change History

Date	History	Reason
April 2011	Corrected errors in steps for creating the stored procedures.	Customer feedback.

April 2011

Corrected errors in steps for creating the stored procedures.

Customer feedback.

Поделиться через