Mega Code Archive
Update and ISNULL
<![CDATA[
4> CREATE TABLE OrderDetails(
5> orderID int NOT NULL,
6> PartID int NOT NULL,
7> Quantity int NOT NULL);
8> GO
1>
2>
3>
4> INSERT INTO OrderDetails VALUES(10001, 11, 12)
5> INSERT INTO OrderDetails VALUES(10001, 42, 10)
6> INSERT INTO OrderDetails VALUES(10001, 72, 5)
7> INSERT INTO OrderDetails VALUES(10002, 14, 9)
8> INSERT INTO OrderDetails VALUES(10002, 51, 40)
9> INSERT INTO OrderDetails VALUES(10003, 41, 10)
10> INSERT INTO OrderDetails VALUES(10003, 61, 35)
11> INSERT INTO OrderDetails VALUES(10003, 65, 15)
12>
13> 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> CREATE PROC dbo.usp_OrderDetailsUpdate
3> @orderid int,
4> @partid int,
5> @neworderid int = NULL,
6> @newpartid int = NULL,
7> @quantity int = NULL
8> AS
9> UPDATE OrderDetails
10> SET orderid = ISNULL(@neworderid, orderid),
11> partid = ISNULL(@newpartid, partid),
12> quantity = ISNULL(@quantity, quantity)
13> WHERE
14> orderid = @orderid
15> AND
16> partid = @partid
17> GO
1>
2>
3>
4>
5> drop table OrderDetails;
6> GO
1>
]]>