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>