Simple SELECT query that uses a variable for the field to sequence the result set. : Dynamic SQL « Transact SQL « SQL Server / T-SQL Tutorial

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

4>
5>
6> CREATE TABLE authors(
7>    au_id          varchar(11),
8>    au_lname       varchar(40)       NOT NULL,
9>    au_fname       varchar(20)       NOT NULL,
10>    phone          char(12)          NOT NULL DEFAULT ('UNKNOWN'),
11>    address        varchar(40)           NULL,
12>    city           varchar(20)           NULL,
13>    state          char(2)               NULL,
14>    zip            char(5)               NULL,
15>    contract       bit               NOT NULL
16> )
17> 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>
3>     DECLARE @chvOrderBy VARCHAR(30),
4>
5>             @chvSQL VARCHAR(255)
6>
7>     SELECT @chvOrderBy = 'au_lname'
8>
9>     SELECT @chvSQL = 'SELECT * FROM authors ORDER BY ' + @chvOrderBy
10>
11>     EXEC (@chvSQL)
12>
13>     GO
au_id       au_lname                                 au_fname             phone        address                                  city                 state zip   contract
----------- ---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------
6           Black                                    Michel               666 666-6666 3 Pl.                                    Regina               SK    66666        1
4           Blue                                     Albert               444 444-4444 7 Av.                                    Vancouver            BC    44444        1
9           Gold                                     Dep                  999 999-9999 5 Av.                                    Oakland              CA    99999        0
2           Jack                                     Majo                 222 222-2222 3 St.                                    Oakland              CA    22222        1
1           Joe                                      Abra                 111 111-1111 6 St.                                    Berkeley             CA    11111        1
3           Pink                                     Cherry               333 333-3333 5 Ln.                                    Vancouver            BC    33333        1
5           Red                                      Anne                 555 555-5555 6 Av.                                    Regina               SK    55555        1
10          Siler                                    Dean                 000 000-0000 4 Av.                                    Oakland              CA    00000        1
7           White                                    Sylvia               777 777-7777 1 Pl.                                    Rockville            MD    77777        1
8           Yellow                                   Heather              888 888-8888 3 Pu                                     Vacaville            CA    88888        0

(10 rows affected)
1>
2>
3> drop table authors;
4> GO