(Solved) : Using Sql Server Create Procedures Purpose Instructions Instruct User Use Sql Server Creat Q42695070 . . .

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.

  1. For the Premier database, Right-click and select New Query.

  2. Use the CREATE PROCEDURE statement to create a trigger namedTRG_CREDIT_LIMIT. Below is the general structure of the CREATEPROCEDURE statement.


[schema_name.] procedure_name [ ; number ]

[ { @parameter [ type_schema_name. ] data_type }

[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]

] [ ,…n ]

[ WITH <procedure_option> [ ,…n ] ]


AS { [ BEGIN ] sql_statement [;] [ …n ] [ END ] }


  1. Here is the code for PRC_GET_REP. Execute it in the New Querywindow.


  2. If you need to change the procedure, use the ALTER PROCEDUREoption.

  3. Retrieve the commission for sales rep, Juan Perez.







@FIRSTNAME = ‘Juan’, @LASTNAME = ‘Perez’;

  1. You should return the following values from the REP table.

    FIRST_NAME  LAST_NAME COMMISSIONJuan    Perez 23487.00

  2. 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.



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…

Leave a Comment


We are the best freelance writing portal. Looking for online writing, editing or proofreading jobs? We have plenty of writing assignments to handle.

Quick Links

Browse Solutions

Place Order

About Us

× How can I help you?