Using SQL Server to Create Procedures
The purpose of these instructions is to instruct the user to useSQL Server to create views in the Premier Products database.
Connecting and Managing the Premier Products Database
Follow the SQL Server Connection Instructions and run thePremier Products Master Script
–DROP TABLE “REP” cascade constraints;–DROP TABLE “CUSTOMER” cascade constraints;–DROP TABLE “ORDERS” cascade constraints;–DROP TABLE “PART” cascade constraints;–DROP TABLE “ORDER_LINE” cascade constraints;CREATE TABLE REP(REP_NUM CHAR(2) PRIMARY KEY,LAST_NAME CHAR(15),FIRST_NAME CHAR(15),STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),COMMISSION DECIMAL(7,2),RATE DECIMAL(3,2) );CREATE TABLE CUSTOMER(CUSTOMER_NUM CHAR(3) PRIMARY KEY,CUSTOMER_NAME CHAR(35) NOT NULL,STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),BALANCE DECIMAL(8,2),CREDIT_LIMIT DECIMAL(8,2),REP_NUM CHAR(2) );CREATE TABLE ORDERS(ORDER_NUM CHAR(5) PRIMARY KEY,ORDER_DATE DATE,CUSTOMER_NUM CHAR(3) );CREATE TABLE PART(PART_NUM CHAR(4) PRIMARY KEY,DESCRIPTION CHAR(15),ON_HAND DECIMAL(4,0),CLASS CHAR(2),WAREHOUSE CHAR(1),PRICE DECIMAL(6,2),MINIMUM DECIMAL(4,0),REORDER CHAR(1) );CREATE TABLE ORDER_LINE(ORDER_NUM CHAR(5),PART_NUM CHAR(4),NUM_ORDERED DECIMAL(3,0),QUOTED_PRICE DECIMAL(6,2),PRIMARY KEY (ORDER_NUM, PART_NUM) );INSERT INTO CUSTOMERVALUES(‘148′,’Al”s Appliance and Sport’,’2837 Greenway’,’Fillmore’,’FL’,’33336′,6550.00,7500.00,’20’);INSERT INTO CUSTOMERVALUES(‘282′,’Brookings Direct’,’3827 Devon’,’Grove’,’FL’,’33321′,431.50,10000.00,’35’);INSERT INTO CUSTOMERVALUES(‘356′,’Ferguson”s’,’382 Wildwood’,’Northfield’,’FL’,’33146′,5785.00,7500.00,’65’);INSERT INTO CUSTOMERVALUES(‘408′,’The Everything Shop’,’1828 Raven’,’Crystal’,’FL’,’33503′,5285.25,5000.00,’35’);INSERT INTO CUSTOMERVALUES(‘462′,’Bargains Galore’,’3829 Central’,’Grove’,’FL’,’33321′,3412.00,10000.00,’65’);INSERT INTO CUSTOMERVALUES(‘524′,’Kline”s’,’838 Ridgeland’,’Fillmore’,’FL’,’33336′,12762.00,15000.00,’20’);INSERT INTO CUSTOMERVALUES(‘608′,’Johnson”s Department Store’,’372 Oxford’,’Sheldon’,’FL’,’33553′,2106.00,10000.00,’65’);INSERT INTO CUSTOMERVALUES(‘687′,’Lee”s Sport and Appliance’,’282 Evergreen’,’Altonville’,’FL’,’32543′,2851.00,5000.00,’35’);INSERT INTO CUSTOMERVALUES(‘725′,’Deerfield”s Four Seasons’,’282 Columbia’,’Sheldon’,’FL’,’33553′,248.00,7500.00,’35’);INSERT INTO CUSTOMERVALUES(‘842′,’All Season’,’28 Lakeview’,’Grove’,’FL’,’33321′,8221.00,7500.00,’20’);INSERT INTO ORDERSVALUES(‘21608′,’20-OCT-2003′,’148’);INSERT INTO ORDERSVALUES(‘21610′,’20-OCT-2003′,’356’);INSERT INTO ORDERSVALUES(‘21613′,’21-OCT-2003′,’408’);INSERT INTO ORDERSVALUES(‘21614′,’21-OCT-2003′,’282’);INSERT INTO ORDERSVALUES(‘21617′,’23-OCT-2003′,’608’);INSERT INTO ORDERSVALUES(‘21619′,’23-OCT-2003′,’148’);INSERT INTO ORDERSVALUES(‘21623′,’23-OCT-2003′,’608’);INSERT INTO PARTVALUES(‘AT94′,’Iron’,50,’HW’,’3′,24.95,20,’N’);INSERT INTO PARTVALUES(‘BV06′,’Home Gym’,45,’SG’,’2′,794.95,30,’N’);INSERT INTO PARTVALUES(‘CD52′,’Microwave Oven’,32,’AP’,’1′,165.00,11,’N’);INSERT INTO PARTVALUES(‘DL71′,’Cordless Drill’,21,’HW’,’3′,129.95,20,’N’);INSERT INTO PARTVALUES(‘DR93′,’Gas Range’,8,’AP’,’2′,495.00,4,’N’);INSERT INTO PARTVALUES(‘DW11′,’Washer’,12,’AP’,’3′,399.99,8,’N’);INSERT INTO PARTVALUES(‘FD21′,’Stand Mixer’,22,’HW’,’3′,159.95,22,’Y’);INSERT INTO PARTVALUES(‘KL62′,’Dryer’,12,’AP’,’1′,349.95,7,’N’);INSERT INTO PARTVALUES(‘KT03′,’Dishwasher’,8,’AP’,’3′,595.00,3,’N’);INSERT INTO PARTVALUES(‘KV29′,’Treadmill’,9,’SG’,’2′,1390.00,5,’Y’);INSERT INTO REPVALUES(’20’,’Kaiser’,’Valerie’,’624 Randall’,’Grove’,’FL’,’33321′,20542.50,0.05);INSERT INTO REPVALUES(’35’,’Hull’,’Richard’,’532 Jackson’,’Sheldon’,’FL’,’33553′,39216.00,0.07);INSERT INTO REPVALUES(’65’,’Perez’,’Juan’,’1626 Taylor’,’Fillmore’,’FL’,’33336′,23487.00,0.05);INSERT INTO ORDER_LINEVALUES(‘21608′,’AT94’,11,21.95);INSERT INTO ORDER_LINEVALUES(‘21610′,’DR93’,1,495.00);INSERT INTO ORDER_LINEVALUES(‘21610′,’DW11’,1,399.99);INSERT INTO ORDER_LINEVALUES(‘21613′,’KL62’,4,329.95);INSERT INTO ORDER_LINEVALUES(‘21614′,’KT03’,2,595.00);INSERT INTO ORDER_LINEVALUES(‘21617′,’BV06’,2,794.95);INSERT INTO ORDER_LINEVALUES(‘21617′,’CD52’,4,150.00);INSERT INTO ORDER_LINEVALUES(‘21619′,’DR93’,1,495.00);INSERT INTO ORDER_LINEVALUES(‘21623′,’KV29’,2,1290.00);
Creating Procedures
The following will instruct the user to create new storedprocedures in SQL Server. For this example, we will create a storedprocedure to implement the following business rule.
BUSINESS RULE: Use the Rep’s first and last name to return theirname and commission.
-
For the Premier database, Right-click and select New Query.
-
Use the CREATE PROCEDURE statement to create a trigger namedTRG_CREDIT_LIMIT. Below is the general structure of the CREATEPROCEDURE statement.
CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,…n ]
[ WITH <procedure_option> [ ,…n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ …n ] [ END ] }
[;]
-
Here is the code for PRC_GET_REP. Execute it in the New Querywindow.
AS
-
If you need to change the procedure, use the ALTER PROCEDUREoption.
-
Retrieve the commission for sales rep, Juan Perez.
CREATE PROCEDURE PRC_GET_REP
@FIRSTNAME CHAR(15), @LASTNAME CHAR(15)
SELECT FIRST_NAME, LAST_NAME, COMMISSION
FROM dbo.REP
WHERE FIRST_NAME = @FIRSTNAME AND LAST_NAME = @LASTNAME;
EXEC PRC_GET_REP
@FIRSTNAME = ‘Juan’, @LASTNAME = ‘Perez’;
-
You should return the following values from the REP table.
FIRST_NAME LAST_NAME COMMISSIONJuan Perez 23487.00
-
What is Valerie Kaiser’s commission? Use the PRC_GET_REPprocedure to retrieve the answer.
Creating Procedures Assignment
Now create a procedure named, PRC_REP_ADD, to handle thefollowing business rule. Use the INSERT command for thisprocedure.
BUSINESS RULE: Insert a new sales rep. Make sure to include ALLthe fields in the associated with a Rep.
1. After creating the procedure, PRC_REP_ADD, insert thefollowing rep by executing PRC_REP_ADD:
Heather Williams
123 Main Street
Good Thunder, MN 56037 $100 commission
Rate of .01
Rep# = 77
To Maximize Your Points
Use tabs and whitespace to make your code as readable aspossible. SQL statements should be lined up. Programming statementsshould include tabs. Ask the instructor if you are unsure aboutyour formatting.
Submission Instructions
After creating the procedure, PRC_REP_ADD, execute the SQLstatement below. Then, paste the results into a Notepad document.Then, skip a line in the notepad document and copy and paste thecode for PRC_REP_ADD into the same Notepad document. Save it as,LastnameFIProc.txt, e.g. Drop this file into the Stored Proceduresdropbox on D2L.
SELECT * FROM REP;
Expert Answer
Answer to Using SQL Server to Create Procedures The purpose of these instructions is to instruct the user to use SQL Server to cre…