Northwind database proceduers. 1. A procedure that accepts a member number as a
ID: 642559 • Letter: N
Question
Northwind database proceduers.
1. A procedure that accepts a member number as a paramether and lists all information for all movies rented by that member. Display a message if the member number is invalid.
2. A procedure that accepts a movie number as a parameter and displays the member number, first name, and last name for all members who rented that movie. Print a message if no members rented that movie. Print a message if no members rented that movie.
3. A procedure that accepts a movie genre as a parameter and displays the movie title, movie year, movie cost, and number of copies of the movie that exist for all movies with that genre. Display a message if the genre is invalid.
4. A procedure that accepts a date as a parameter and displays the number of movies rented and the total income from the rentals on that date. Print a message if no movies were rented on that date.
Explanation / Answer
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.
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.
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:
Right-click the Stored Procedures node and then click Add New Stored Procedure.
Replace the CREATE PROCEDURE template following code:
Save the procedure.
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:
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:
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:
Right-click the Stored Procedures node and then click Add New Stored Procedure.
Replace the CREATE PROCEDURE template with the following code:
Save the procedure.
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.
If you already have a data connection to the Northwind database, you can go to the next section, Creating the Stored Procedures.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.