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
<source lang="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> 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</source>
Dynamic Selects
<source lang="sql">
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</source>
Execute a dynamic statement and return the result with OUTPUT
<source lang="sql">
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</source>
EXECUTE AS
<source lang="sql">
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.</source>
Insert into with EXEC
<source lang="sql">
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</source>
Parse exception in another level of scope: Exception in EXEC
<source lang="sql">
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></source>
Sending Variable Information to an EXEC() Call
<source lang="sql">
DECLARE @IntVal int SET @IntVal = 21 EXEC ("usp_MyProc " + STR (@IntVal)) GO</source>
Statement Limit
<source lang="sql">
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</source>
Switch database in EXEC
<source lang="sql">
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</source>
The syntax of the EXEC statement
<source lang="sql">
{EXEC|EXECUTE} ("SQL_string")</source>
Use more than one sql statement in EXEC
<source lang="sql">
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</source>
Using EXEC () to Execute a Dynamic TOP n Query
<source lang="sql">
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)</source>
