Transaction in a procedure : Create Procedure « Store Procedure Function « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Store Procedure Function
3. Create Procedure

Transaction in a procedure

 

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)

 

Related examples in the same category