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

Материал из SQL эксперт
Перейти к: навигация, поиск

Creation Script for the usp_EmployeeUpdate Stored Procedure

   <source lang="sql">

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</source>


One procedure for insert, another for update

   <source lang="sql">

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></source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Transact_SQL/Update_Statement&oldid=6355»