SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I Create a stored procedure named “dropatable” that will drop a table name that is entered as...


How do I Create a stored procedure named “dropatable” that will drop a table name that is entered as a parameter if it exists.

Author
Message
jyerby
jyerby
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
Here is the code where I created my tables -
I want to Create a stored procedure named “dropatable” that will drop a table name that is entered as a parameter if it exists.


create PROCEDURE [dbo].[createalltables]

AS
BEGIN

CREATE TABLE SALES_REP (
SREP_NUM CHAR(2) NOT NULL UNIQUE,
SREP_LNAME CHAR(20) NOT NULL,
SREP_FNAME CHAR(20) NOT NULL,
SREP_STREET CHAR(30) NOT NULL,
SREP_CITY CHAR(25) NOT NULL,
SREP_STATE CHAR(2) NOT NULL,
SREP_ZIP CHAR(5) NOT NULL,
SREP_COMM_TO_DATE NUMERIC(7,2),
SREP_COMM_RATE NUMERIC(3,2)
PRIMARY KEY(SREP_NUM));



CREATE TABLE CUSTOMER (
CUST_NUM CHAR(3) NOT NULL UNIQUE,
CUST_NAME CHAR(30) NOT NULL,
CUST_STREET CHAR(30) NOT NULL,
CUST_CITY CHAR(25) NOT NULL,
CUST_STATE CHAR(2) NOT NULL,
CUST_ZIP CHAR(5) NOT NULL,
CUST_BALANCE NUMERIC(7,2),
CUST_CREDIT_LIMIT NUMERIC(7,2),
SREP_NUM CHAR(2) NOT NULL,
PRIMARY KEY(CUST_NUM),
FOREIGN KEY(SREP_NUM) REFERENCES SALES_REP);

INSERT INTO SALES_REP
VALUES(20,'Kaiser','Valerie','624 Randall','Grove','FL',33321,20542.50,0.05)

INSERT INTO SALES_REP
VALUES(35,'Hull','Richard','532 Jackson','Sheldon','FL',33553,39216.00,0.07)

INSERT INTO SALES_REP
VALUES(65,'Perez','Juan','1626 Taylor','Fillmore','FL',33336,23487.00,0.05);

INSERT INTO SALES_REP
VALUES(45,'Yerby','Johnathan','476 Lincoln','Grove','FL',33321,37892.50,0.06);



INSERT INTO CUSTOMER
VALUES(148,'Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',$6550.00,$7500.00,20)

INSERT INTO CUSTOMER
VALUES(282,'Brookings Direct','3827 Devon','Grove','FL','33321',$431.50,$10000.00,35)

INSERT INTO CUSTOMER
VALUES(356,'Ferguson''s','382 Wildwood','Northfield','FL','33146',$5785.00,$7500.00,65)

INSERT INTO CUSTOMER
VALUES(408,'The Everything Shop','1828 Raven','Crystal','FL','33503',$5285.25,$5000.00,35)

INSERT INTO CUSTOMER
VALUES(462,'Bargains Galore','3829 Central','Grove','FL','33321',$3412.00,$10000.00,65)

INSERT INTO CUSTOMER
VALUES(524,'Kline''s','838 Ridgeland','Fillmore','FL','33336',$12762.00,$15000.00,20)

INSERT INTO CUSTOMER
VALUES(608,'Johnson''s Department Store','372 Oxford','Sheldon','FL','33553',$2106.00,$10000.00,65)

INSERT INTO CUSTOMER
VALUES(687,'Lee''s Sport and Appliance','282 Evergreen','Altonville','FL','32543',$2851.00,$5000.00,35)

INSERT INTO CUSTOMER
VALUES(725,'Deerfield''s Four Seasons','282 Columbia','Sheldon','FL','33553',$248.00,$7500.00,35)

INSERT INTO CUSTOMER
VALUES(842,'All Season','28 Lakeview','Grove','FL','33321',$8221.00,$7500.00,20);




CREATE TABLE ORDERS
(
ORD_NUM CHAR(5) PRIMARY KEY,
ORD_DATE DATETIME NOT NULL,
CUST_NUM CHAR(3) FOREIGN KEY REFERENCES CUSTOMER on Update Cascade,
);

INSERT INTO ORDERS
VALUES(21608,'10/20/2007',148)

INSERT INTO ORDERS
VALUES(21610,'10/20/2007',356)

INSERT INTO ORDERS
VALUES(21613,'10/21/2007',408)

INSERT INTO ORDERS
VALUES(21614,'10/21/2007',282)

INSERT INTO ORDERS
VALUES(21617,'10/23/2007',608)

INSERT INTO ORDERS
VALUES(21619,'10/23/2007',148)

INSERT INTO ORDERS
VALUES(21623,'10/23/2007',608);


CREATE TABLE PART
(
PART_NUM CHAR(5) PRIMARY KEY,
PART_DESC VARCHAR(20),
PART_ON_HAND INT NOT NULL,
PART_CLASS CHAR(2),
PART_WAREHOUSE CHAR(2),
PART_PRICE NUMERIC(7,2),
);



CREATE TABLE ORDER_LINE
(
ORD_NUM CHAR(5) FOREIGN KEY REFERENCES ORDERS,
PART_NUM CHAR(5) FOREIGN KEY REFERENCES PART,
ORD_NUM_ORDERED INT,
ORD_QUOTED_PRICE NUMERIC(7,2),
);

INSERT INTO PART
VALUES( 'AT94','Iron',50.00,'HW','3',24.95)

INSERT INTO PART
VALUES( 'BV06','Home Gym',45.00,'SG','2',794.95)

INSERT INTO PART
VALUES( 'CD52','Microwave Oven',32.00,'AP','1',165.00)

INSERT INTO PART
VALUES( 'DL71','Cordless Drill',21.00,'HW','3',129.95)

INSERT INTO PART
VALUES( 'DR93','Gas Range',8.00,'AP','2',495.00)

INSERT INTO PART
VALUES( 'DW11','Washer',12.00,'AP','3',399.99)

INSERT INTO PART
VALUES( 'FD21','Stand Mixer',22.00,'HW','3',159.95)

INSERT INTO PART
VALUES( 'KL62','Dryer',12.00,'AP','1',349.95)

INSERT INTO PART
VALUES( 'KT03','Dishwasher',8.00,'AP','3',595.00)

INSERT INTO PART
VALUES( 'KV29','Treadmill',9.00,'SG','2',1390.00);





INSERT INTO ORDER_LINE
VALUES( 21608,'AT94',11.00,21.95)

INSERT INTO ORDER_LINE
VALUES( 21610,'DR93',1.00,495.00)

INSERT INTO ORDER_LINE
VALUES( 21610,'DW11',1.00,399.99)

INSERT INTO ORDER_LINE
VALUES( 21613,'KL62',4.00,329.95)

INSERT INTO ORDER_LINE
VALUES( 21614,'KT03',2.00,595.00)

INSERT INTO ORDER_LINE
VALUES( 21617,'BV06',2.00,794.95)

INSERT INTO ORDER_LINE
VALUES( 21617,'CD52',4.00,150.00)

INSERT INTO ORDER_LINE
VALUES( 21619,'DR93',1.00,495.00)

INSERT INTO ORDER_LINE
VALUES( 21623,'KV29',2.00,1290.00);

SELECT * FROM CUSTOMER
SELECT * FROM ORDER_LINE
SELECT * FROM ORDERS
SELECT * FROM PART
SELECT * FROM SALES_REP

END
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search