Update and ISNULL : ISNULL « Data Convert Functions « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Data Convert Functions
3. ISNULL

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>