SQL Server/T-SQL Tutorial/Transact SQL/EXEC
Содержание
- 1 A stored procedure with dynamic execution by using EXEC
- 2 Dynamic Selects
- 3 Execute a dynamic statement and return the result with OUTPUT
- 4 EXECUTE AS
- 5 Insert into with EXEC
- 6 Parse exception in another level of scope: Exception in EXEC
- 7 Sending Variable Information to an EXEC() Call
- 8 Statement Limit
- 9 Switch database in EXEC
- 10 The syntax of the EXEC statement
- 11 Use more than one sql statement in EXEC
- 12 Using EXEC () to Execute a Dynamic TOP n Query
A stored procedure with dynamic execution by using EXEC
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
Dynamic Selects
6>
7> CREATE TABLE authors(
8> au_id varchar(11),
9> au_lname varchar(40) NOT NULL,
10> au_fname varchar(20) NOT NULL,
11> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
12> address varchar(40) NULL,
13> city varchar(20) NULL,
14> state char(2) NULL,
15> zip char(5) NULL,
16> contract bit NOT NULL
17> )
18> 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> SELECT au_fname, au_lname FROM authors
4> EXEC ("SELECT au_fname, au_lname FROM authors")
5> GO
au_fname au_lname
-------------------- ----------------------------------------
Abra Joe
Majo Jack
Cherry Pink
Albert Blue
Anne Red
Michel Black
Sylvia White
Heather Yellow
Dep Gold
Dean Siler
Abra Joe
Majo Jack
Cherry Pink
Albert Blue
Anne Red
Michel Black
Sylvia White
Heather Yellow
Dep Gold
Dean Siler
(20 rows affected)
au_fname au_lname
-------------------- ----------------------------------------
Abra Joe
Majo Jack
Cherry Pink
Albert Blue
Anne Red
Michel Black
Sylvia White
Heather Yellow
Dep Gold
Dean Siler
Abra Joe
Majo Jack
Cherry Pink
Albert Blue
Anne Red
Michel Black
Sylvia White
Heather Yellow
Dep Gold
Dean Siler
(20 rows affected)
1>
2> drop table authors;
3> GO
Execute a dynamic statement and return the result with OUTPUT
4>
5> DECLARE @sql AS NVARCHAR(12), @i AS INT
6>
7> SET @sql = N"SET @p = 10;";
8>
9> EXEC sp_executesql
10> @stmt = @sql,
11> @params = N"@p AS INT OUTPUT",
12> @p = @i OUTPUT;
13>
14> SELECT @i;
15> GO
-----------
10
EXECUTE AS
Valid options for this statement are described in the following table.
Option Description
Caller in the context of the user executing the procedure.
Self with default permissions.
Owner in the context of the owner of the procedure.
"user_name" in the context of a specific user or login.
Insert into with EXEC
4> CREATE TABLE #VersionTable(
5> [Index] int PRIMARY KEY,
6> Name varchar(30),
7> Internal_Value int,
8> Character_Value varchar(250)
9> )
10> GO
1>
2> INSERT INTO #VersionTable
3> EXEC master..xp_msver
4>
5> DECLARE @Version int
6>
7> SELECT @Version = (SELECT Internal_Value
8> FROM #VersionTable
9> WHERE Name = "ProductVersion")
10> IF (@Version) >= 524288
11> PRINT "Running SS2K Beta 2 or Later"
12> ELSE
13> IF @Version >= 458752
14> PRINT "Running 7.0 RTM or Later"
15> ELSE
16> PRINT "Running Pre 7.0 RTM Version"
17> GO
(20 rows affected)
(20 rows affected)
Running SS2K Beta 2 or Later
1> DROP TABLE #VersionTable
2> GO
Parse exception in another level of scope: Exception in EXEC
6> EXEC("SELECTxzy FROM SomeTable")
7> PRINT "This will NOT print!"
8> GO
Msg 156, Level 15, State 1, Server J\SQLEXPRESS, Line 1
Incorrect syntax near the keyword "FROM".
This will NOT print!
1>
Sending Variable Information to an EXEC() Call
DECLARE @IntVal int
SET @IntVal = 21
EXEC ("usp_MyProc " + STR (@IntVal))
GO
Statement Limit
5> DECLARE @sql AS NVARCHAR(MAX), @i AS INT;
6> SET @sql = N"";
7> SET @i = 1;
8> WHILE @i <= 100
9> BEGIN
10> SET @sql = @sql + N"PRINT " + CAST(@i AS NVARCHAR(10))
11> + NCHAR(13) + NCHAR(10);
12> SET @i=@i+1;
13> END
14> EXEC sp_executesql @sql;
15> GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Switch database in EXEC
4> SET NOCOUNT ON
5> PRINT db_name()
6> EXEC ("USE Northwind PRINT db_name() SELECT count(*) FROM products")
7> PRINT db_name()
8> GO
master
The syntax of the EXEC statement
{EXEC|EXECUTE} ("SQL_string")
Use more than one sql statement in EXEC
5> DECLARE @schema AS VARCHAR(1000), @insert AS VARCHAR(1000);
6> SET @schema = "col1 INT, col2 DECIMAL(10, 2)";
7> SET @insert = "INSERT INTO #T42 VALUES(10, 20.30)";
8>
10> CREATE TABLE #T42(dummycol INT);
11>
17> EXEC("
18~ ALTER TABLE #T42 ADD " + @schema + ";
19~ ALTER TABLE #T42 DROP COLUMN dummycol;
20~
21~ EXEC(""" + @insert + """)");
22> GO
1>
2> -- Back in the outer level, access #T in a new batch
3> SELECT * FROM #T42;
4> GO
col1 col2
----------- ------------
10 20.30
1> -- Cleanup
2> DROP TABLE #T42;
3> GO
Using EXEC () to Execute a Dynamic TOP n Query
3>
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> DECLARE
5> @ExecStr varchar (2000),
6> @N int
7> SELECT
8> @N = 5
9> SELECT
10> @ExecStr = "SELECT TOP " + STR (@N, 2) + " * "
11> + "FROM Employee ORDER BY ID DESC"
12> EXEC (@ExecStr)
13>
14>
15>
16> drop table employee;
17> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
(5 rows affected)