Mega Code Archive
Creation Script for the usp_EmployeeDelete Stored Procedure
<![CDATA[
5>
6>
7> CREATE TABLE OrderDetails(
8> ID int NOT NULL,
9> PartID int NOT NULL,
10> Quantity int NOT NULL);
11> GO
1>
2> INSERT INTO OrderDetails VALUES(10001, 11, 12)
3> INSERT INTO OrderDetails VALUES(10001, 42, 10)
4> INSERT INTO OrderDetails VALUES(10001, 72, 5)
5> INSERT INTO OrderDetails VALUES(10002, 14, 9)
6> INSERT INTO OrderDetails VALUES(10002, 51, 40)
7> INSERT INTO OrderDetails VALUES(10003, 41, 10)
8> INSERT INTO OrderDetails VALUES(10003, 61, 35)
9> INSERT INTO OrderDetails VALUES(10003, 65, 15)
10>
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE Employees
4> (orderid int NOT NULL,
5> mgrid int NULL,
6> empname varchar(25) NOT NULL,
7> salary money NOT NULL);
8> GO
1>
2> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 1, NULL, 'Nancy', $10000.00)
3> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 2, 1, 'Andrew', $5000.00)
4> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 3, 1, 'Janet', $5000.00)
5> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 4, 1, 'Margaret',$5000.00)
6> INSERT INTO employees(orderid, mgrid, empname, salary) VALUES( 5, 2, 'Steven', $2500.00)
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE PROC dbo.usp_EmployeeDelete
4> @orderid int
5> AS
6> BEGIN TRAN
7> -- delete matching rows from OrderDetails
8> DELETE FROM OrderDetails WHERE id = @orderid
9> -- delete row from Employee
10> DELETE FROM Employee WHERE orderid = @orderid
11> COMMIT TRAN
12> GO
1>
2>
3> drop proc dbo.usp_EmployeeDelete;
4> GO
1> drop table Employees;
2> GO
1>
2> drop table OrderDetails;
3> GO
1>
]]>