IF (@au_id IS NULL) : IF « Transact SQL « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Transact SQL
3. IF

15>
16> CREATE TABLE authors(
17>    au_id          varchar(11),
18>    au_lname       varchar(40)       NOT NULL,
19>    au_fname       varchar(20)       NOT NULL,
20>    phone          char(12)          NOT NULL DEFAULT ('UNKNOWN'),
21>    address        varchar(40)           NULL,
22>    city           varchar(20)           NULL,
23>    state          char(2)               NULL,
24>    zip            char(5)               NULL,
25>    contract       bit               NOT NULL
26> )
27> 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>
2>     CREATE PROC prEditAuthor
3>        @au_id       id,
4>        @au_lname    varchar(40),
5>        @au_fname    varchar(20),
6>        @phone       char(12),
7>        @address     varchar(40),
8>        @city        varchar(20),
9>        @state       char(2),
10>        @zip         char(5),
11>        @contract    bit
12>     AS
13>        IF (@au_id IS NULL) BEGIN
14>           INSERT INTO authors(au_lname,au_fname,phone,address,city,state,zip,contract)
15>           VALUES(@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)
16>        END ELSE BEGIN
17>           UPDATE authors
18>           SET au_lname = @au_lname,
19>               au_fname = @au_fname,
20>               phone    = @phone,
21>               address  = @address,
22>               city     = @city,
23>               state    = @state,
24>               zip      = @zip,
25>               contract = @contract
26>           WHERE au_id = @au_id
27>        END
28>     GO
1>
2>     drop PROC prEditAuthor;
3>     GO
1>
2>     drop table authors;
3>     GO