SQL Server/T-SQL Tutorial/Transact SQL/Update Statement

Creation Script for the usp_EmployeeUpdate Stored Procedure

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

One procedure for insert, another for update

4> CREATE TABLE authors(
5>    au_id          varchar(11),
6>    au_lname       varchar(40)       NOT NULL,
7>    au_fname       varchar(20)       NOT NULL,
8>    phone          char(12)          NOT NULL DEFAULT ("UNKNOWN"),
9>    address        varchar(40)           NULL,
10>    city           varchar(20)           NULL,
11>    state          char(2)               NULL,
12>    zip            char(5)               NULL,
13>    contract       bit               NOT NULL
14> )
15> GO
1> insert authors values("1",  "Joe",   "Abra",   "111 111-1111", "6 St.", "Berkeley",  "CA", "11111", 1)
2> insert authors values("2",  "Jack",  "Majo",   "222 222-2222", "3 St.", "Oakland" ,  "CA", "22222", 1)
3> insert authors values("3",  "Pink",  "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4",  "Blue",  "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5",  "Red",   "Anne",   "555 555-5555", "6 Av.", "Regina",    "SK", "55555", 1)
6> insert authors values("6",  "Black", "Michel", "666 666-6666", "3 Pl.", "Regina",    "SK", "66666", 1)
7> insert authors values("7",  "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8",  "Yellow","Heather","888 888-8888", "3 Pu",  "Vacaville", "CA", "88888", 0)
9> insert authors values("9",  "Gold",  "Dep",    "999 999-9999", "5 Av.", "Oakland",   "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean",   "000 000-0000", "4 Av.", "Oakland",   "CA", "00000", 1)
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 rows affected)
(1 rows affected)
1>     CREATE PROC prInsertAuthor
2>         @au_lname    varchar(40),
3>         @au_fname    varchar(20),
4>         @phone       char(12),
5>         @address     varchar(40),
6>         @city        varchar(20),
7>         @state       char(2),
8>         @zip         char(5),
9>         @contract    bit
10>     AS
11>         INSERT INTO authors(au_lname,au_fname,phone,address,city,state,zip,contract)
12>               VALUES(@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)
13>     GO
1>     CREATE PROC prUpdateAuthor
2>         @au_id       id,
3>         @au_lname    varchar(40),
4>         @au_fname    varchar(20),
5>         @phone       char(12),
6>         @address     varchar(40),
7>         @city        varchar(20),
8>         @state       char(2),
9>         @zip         char(5),
10>         @contract    bit
11>     AS
12>           UPDATE authors
13>           SET au_lname = @au_lname,
14>               au_fname = @au_fname,
15>               phone    = @phone,
16>               address  = @address,
17>               city     = @city,
18>               state    = @state,
19>               zip      = @zip,
20>               contract = @contract
21>           WHERE au_id = @au_id
22>     GO
1>
2>     drop PROC prInsertAuthor;
3>     drop PROC prUpdateAuthor;
4>     drop table authors;
5>