A stored procedure with dynamic execution by using EXEC : EXEC « Transact SQL « SQL Server / T-SQL Tutorial






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>     CREATE PROC prExecute
3>     @chvExec varchar(255)
4>     AS
5>     EXEC (@chvExec)
6>     GO
1>
2>     DECLARE @chvDoThis varchar(255)
3>     SELECT @chvDoThis = 'DELETE AUTHORS'
4>     EXEC prExecute @chvDoThis
5>
6>
7>     drop PROC prExecute  ;
8>     GO

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








20.19.EXEC
20.19.1.The syntax of the EXEC statement
20.19.2.EXECUTE AS
20.19.3.Dynamic Selects
20.19.4.A stored procedure with dynamic execution by using EXEC
20.19.5.Sending Variable Information to an EXEC() Call
20.19.6.Switch database in EXEC
20.19.7.Statement Limit
20.19.8.Parse exception in another level of scope: Exception in EXEC
20.19.9.Execute a dynamic statement and return the result with OUTPUT
20.19.10.Use more than one sql statement in EXEC
20.19.11.Insert into with EXEC
20.19.12.Using EXEC () to Execute a Dynamic TOP n Query