Mega Code Archive
Creation Script for the usp_EmployeeUpdate Stored Procedure
<![CDATA[
5>
6>
7> CREATE TABLE OrderDetails(
8> orderID 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> customerid int NULL,
6> empname varchar(25) NOT NULL,
7> orderdate datetime,
8> salary money NOT NULL);
9> GO
1>
2>
3>
4> CREATE PROC dbo.usp_EmployeeUpdate
5> @orderid int,
6> @neworderid int = NULL,
7> @customerid char(5) = NULL,
8> @orderdate datetime = NULL
9> AS
10>
11> IF @neworderid IS NOT NULL AND @orderid <> @neworderid
12> BEGIN
13> BEGIN TRAN
14> INSERT INTO Employees(orderid, customerid, orderdate)
15> SELECT
16> @neworderid,
17> ISNULL(@customerid, customerid),
18> ISNULL(@orderdate, orderdate)
19> FROM
20> Employees
21> WHERE
22> orderid = @orderid
23> UPDATE OrderDetails
24> SET orderid = @neworderid
25> WHERE orderid = @orderid
26> DELETE FROM Employee
27> WHERE orderid = @orderid
28> COMMIT TRAN
29> END
30>
31> ELSE
32> UPDATE Employee
33> SET customerid = ISNULL(@customerid, customerid),
34> orderdate = ISNULL(@orderdate, orderdate)
35> WHERE
36> orderid = @orderid
37> GO
1>
2> drop proc usp_EmployeeUpdate;
3> GO
1> drop table Employees;
2> GO
1>
2> drop table OrderDetails;
3> GO
]]>