Mega Code Archive
Transaction in a procedure
<![CDATA[
3>
4> CREATE TABLE Employees(
5> empid int NOT NULL,
6> mgrid int NULL,
7> empname varchar(25) NOT NULL,
8> salary money NOT NULL
9> )
10> GO
1> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(1, NULL, 'Nancy', $10000.00)
2> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(2, 1, 'Andrew', $5000.00)
3> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(3, 1, 'Janet', $5000.00)
4> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(4, 1, 'Margaret', $5000.00)
5> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(5, 2, 'Steven', $2500.00)
6> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(6, 2, 'Michael', $2500.00)
7> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(7, 3, 'Robert', $2500.00)
8> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(8, 3, 'Laura', $2500.00)
9> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(9, 3, 'Ann', $2500.00)
10> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(10, 4, 'Ina', $2500.00)
11> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(11, 7, 'David', $2000.00)
12> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(12, 7, 'Ron', $2000.00)
13> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(13, 7, 'Dan', $2000.00)
14> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(14, 11, 'James', $1500.00)
15> INSERT INTO Employees(empid, mgrid, empname, salary) VALUES(15, 12, 'Sean', $1500.00)
16> 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 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE PROC RemoveEmployeeMoveSubs
4> (
5> @empid int,
6> @newmgr int
7> )
8> AS
9> BEGIN TRAN
10> UPDATE E
11> SET mgrid = @newmgr
12> FROM
13> Employees AS E
14> JOIN
15> Employees AS M ON E.mgrid = M.empid
16> WHERE
17> M.empid = @empid
18> DELETE FROM Employees
19> WHERE
20> empid = @empid
21> COMMIT TRAN
22> GO
1> --Testing the RemoveEmployeeMoveSubs Stored Procedure
2> EXEC RemoveEmployeeMoveSubs
3> @empid = 3,
4> @newmgr = 4
5>
6> drop table Employees;
7> GO
(3 rows affected)
(1 rows affected)
]]>