exec在sql語句-九游会j9娱乐平台

a. sql 中exec的意思

你去查一下sp_addextendedproperty這個存儲過程的定義嘛。exec就是執行存儲過程。

b. sql語言exec作用

修改表刪除表，用的是update或者drop，這都是固定的sql語句的起始，比如ddl語句（create，drop，alter等），dml語句（insert ，delete，update，select），dcl語句等（commit，revoke，grant，rollback），這些語句都有固定的起始，看到這個語句sql解釋器就知道你要干什麼了。
那麼sp_help是什麼？是固定的起始嗎？明顯不是？那麼sp_help是什麼，應該是一個過程或者包一類的東西，那麼這個東西寫在這里要干什麼，明顯是要執行，那麼怎麼執行這個包或者過程，用execute，簡化寫為exec。
這里屬於單獨執行一個過程或者包，那麼肯定要加exec執行，exec是語句固定提示符，告訴程序他的下一個動作是「執行」

c. 怎樣sql存儲過程中執行動態sql語句

存儲過程中執行動態sql語句
mssql為我們提供了兩種動態執行sql語句的命令，分別是exec和sp_executesql;通常,sp_executesql則更具有優勢，它提供了輸入輸出介面，而exec沒有。還有一個最大的好處就是利用sp_executesql，能夠重用執行計劃，這就大大提供了執行性能，還可以編寫更安全的代碼。exec在某些情況下會更靈活。除非您有令人信服的理由使用exec，否側盡量使用sp_executesql.
1.exec的使用
exec命令有兩種用法，一種是執行一個存儲過程，另一種是執行一個動態的批處理。以下所講的都是第二種用法。
下面先使用exec演示一個例子,代碼1

代碼
declare @tablename varchar(50),@sql nvarchar (max),@orderid int;
set @tablename = 'orders';
set @orderid = 10251;
set @sql =
'select * from ' quotename(@tablename) 'where orderid = '
cast(@orderid as varchar(10)) ' order by orderid desc'
exec(@sql);

註：這里的exec括弧中只允許包含一個字元串變數，但是可以串聯多個變數，如果我們這樣寫exec：

exec('select top(' cast(@topcount as varchar(10)) ')* from '
quotename(@tablename) ' order by orderid desc');

sql編譯器就會報錯，編譯不通過，而如果我們這樣：

exec(@sql @sql2 @sql3);

編譯器就會通過；

所以最佳的做法是把代碼構造到一個變數中，然後再把該變數作為exec命令的輸入參數，這樣就不會受限制了。

exec的缺點是不提供介面，這里的介面是指，它不能執行一個包含一個帶變數符的批處理，如下

代碼
declare @tablename varchar(50),@sql nvarchar(max),@orderid int;
set @tablename = 'orders';
set @orderid = 10251;
set @sql = 'select * from ' quotename(@tablename)
'where orderid = @orderid order by orderid desc'
exec(@sql);

關鍵就在set @sql這一句話中，如果我們運行這個批處理，編譯器就會產生一下錯誤
msg 137, level 15, state 2, line 1
必須聲明標量變數 "@orderid"。
使用exec時，如果您想訪問變數，必須把變數內容串聯到動態構建的代碼字元串中，如：
set @sql = 'select * from ' quotename(@tablename) 'where orderid = ' cast(@orderid as varchar(10)) ' order by orderid desc'

串聯變數的內容也存在性能方面的弊端。sql server為每一個的查詢字元串創建新的執行計劃，即使查詢模式相同也是這樣。為演示這一點，先清空緩存中的執行計劃
dbcc freeproccache (這個不是本文所涉及的內容，您可以查看ms的msdn)
將代碼1運行3次，分別對@orderid 賦予下面3個值，10251，10252，10253。然後使用下面的代碼查詢

select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects where sql not like '�ch%' and sql not like '%sys.%'

點擊f5運行，我們可以看到，每執行一次都要產生一次的編譯，執行計劃沒有得到充分重用。

exec除了不支持動態批處理中的輸入參數外，他也不支持輸出參數。默認情況下，exec把查詢的輸出返回給調用者。例如下面代碼返回orders表中所有的記錄數

declare @sql nvarchar(max)
set @sql = 'select count(orderid) from orders';
exec(@sql);

然而，如果你要把輸出返回給調用批處理中的變數，事情就沒有那麼簡單了。為此，你必須使用insert exec語法把輸出插入到一個目標表中，然後從這表中獲取值後賦給該變數，就像這樣：

代碼
declare @sql nvarchar(max),@recordcount int
set @sql = 'select count(orderid) from orders';
create table #t(tid int);
insert into #t exec(@sql);
set @recordcount = (select tid from #t)
select @recordcount
drop table #t2

2.sp_executesql的使用
sp_executesql命令在sql server中引入的比exec命令晚一些，它主要為重用執行計劃提供更好的支持。
為了和exec作一個鮮明的對比,我們看看如果用代碼1的代碼，把exec換成sp_executesql,看看是否得到我們所期望的結果

代碼
declare @tablename varchar(50),@sql nvarchar(max),@orderid int ,@sql2 nvarchar(max);
set @tablename = 'orders ';
set @orderid = 10251;
set @sql = 'select * from ' quotename(@tablename) ' where orderid = ' cast(@orderid as varchar(50)) ' order by orderid desc'
exec sp_executesql @sql

注意最後一行；事實證明可以運行；
sp_executesql提供介面
sp_executesql命令比exec命令更靈活，因為它提供一個介面，該介面及支持輸入參數也支持輸出參數。這功能使你可以創建帶參數的查詢字元串，這樣就可以比exec更好的重用執行計劃,sp_executesql的構成與存儲過程非常相似，不同之處在於你是動態構建代碼。它的構成包括：代碼快，參數聲明部分，參數賦值部分。說了這么多，還是看看它的語法：

exec sp_executesql

@stmt= ,--類似存儲過程主體

@params = , --類似存儲過程參數部分,聲明參數類型

--類似存儲過程調用，為參數賦值,參數值要和參數順序要一一對應，也可以通過為參數指明參數值的方式為其賦值

@stmt參數是輸入的動態批處理，它可以引入輸入參數或輸出參數，和存儲過程的主體語句一樣，只不過它是動態的，而存儲過程是靜態的，不過你也可以在存儲過程中使用sp_executesql;
@params參數與定義輸入/輸出參數的存儲過程頭類似，實際上和存儲過程頭的語法完全一樣；
@ 與調用存儲過程的exec部分類似。
其實@stmt,@params可以省略，那麼exec sp_executesql的語法就可以簡寫成如下格式：

exec sp_executesql
,
,


為了說明sp_executesql對執行計劃的管理優於exec,我將使用前面討論exec時用到的代碼。

代碼
declare @tablename varchar(50),@sql nvarchar(max),@orderid int;
set @tablename = 'orders ';
set @orderid = 10251;
set @sql = 'select * from ' @tablename ' where orderid = @oid order by orderid desc'
--注意當要對動態sql語句的表名實行參數化時，不可以如下表示：
--set @sql='select * from @tablename where orderid=@oid order by orderid desc',
--如果這樣會提示必須聲明標量變數@tablename,只可以如上面所寫的一樣，將表名@tablename作為變數名進行拼接

exec sp_executesql
@sql,
n'@oid int ',
@oid = @orderid

下面我們看看exec sp_executesql的執行效率，在調用該代碼和檢查它生成的執行計劃前，先清空緩存中的執行計劃；
dbcc freeproccache
將上面的動態代碼執行3次，每次執行都賦予@orderid 不同的值，然後查詢sys.syscacheobjects表，並注意它的輸出，優化器只創建了一個備用計劃，而且該計劃被重用的3次

select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects where sql not like '�che%' and sql not like '%sys.%' and sql not like '%sp_executesql%'

點擊f5運行。
sq_executesql的另一個與其介面有關的強大功能是，你可以使用輸出參數為調用批處理中的變數返回值。利用該功能可以避免用臨時表返回數據，從而得到更高效的代碼和更少的重新編譯。定義和使用輸出參數的語法與存儲過程類似。也就是說，你需要在聲明參數時指定output子句。例如，下面的靜態代碼簡單的演示了如何從動態批處理中利用輸出參數@p把值返回到外部批處理中的變數@i.

declare @sql as nvarchar(12),@i as int;
set @sql = n' set @p = 10';
exec sp_executesql
@sql,
n'@p as int output',
@p = @i output
select @i --該代碼返回輸出10

以字母 n 為前綴標識 unicode 字元串常量
總結以下幾點：
一.使用exce sp_executesql效率比exec要高，同一類型的語句，只需編譯一次即可，而exec執行幾次就需要編譯幾次。
二.構造動態sql的where子句，也就是條件子句時，exec無法使用變數來進行站位，需要將變數轉換成字元串，然後和動態sql進行拼接，這就可能引起sql注入問題，如下：

set @sql = 'select * from ' quotename(@tablename)
' where orderid = ' cast(@orderid as varchar(50)) ' order by orderid desc'

而若使用exec sp_executesql則可以使用變數來進行站位，以後再給這個參數傳值的放式構造動態sql，就避免的sql注入的問題，如下：
set @sql = 'select * from ' @tablename ' where orderid = @oid order by orderid desc'

三.無論是exec還是exec sp_executesql，如果想要將表名和列名進行動態參數化，不可以使用表名參數和列名參數來進行站位，而且表名參數和列名參數需要使用存儲過程的參數.對 於exec sp_executesql來說，不可以將表名參數和列名參數在指定為在exec sp_executesql參數聲明部分聲明的參數，如：

代碼
create procedure getdata
@tbname nvarchar(10),
@colname nvarchar(10),
@name nvarchar(10)
as
begin
declare @sql nvarchar(50);
set @sql='select ' @colname ' from ' @tbname ' where name=@wherename';
--注意此句不可以寫成如下:
-- set @sql='select @colname from @tbname where name=@wherename';
exec sp_executesql
@sql,
n'@wherename nvarchar(10)',
@name
end

d. sql的關於exec

execute
執行標量值的用戶定義函數、系統過程、用戶定義存儲過程或擴展存儲過程。同時支持 transact-sql 批處理內的字元串的執行

若要喚醒調用函數，請使用 execute stored_procere 中描述的語法。

語法
執行存儲過程：

[ [ exec [ ute ] ]
{
[ @return_status = ]
{ procere_name [ ;number ] | @procere_name_var
}
[ [ @parameter = ] { value | @variable [ output ] | [ default ] ]
[ ,...n ]
[ with recompile ]

執行字元串：

exec [ ute ] ( { @string_variable | [ n ] 'tsql_string' } [ ...n ] )

參數
@return_status

是一個可選的整型變數，保存存儲過程的返回狀態。這個變數在用於 execute 語句前，必須在批處理、存儲過程或函數中聲明過。

在用於喚醒調用標量值用戶定義函數時，@return_status 變數可以是任何標量數據類型。

procere_name

是擬調用的存儲過程的完全合法或者不完全合法的名稱。過程名稱必須符合標識符規則。有關更多信息，請參見使用標識符。無論伺服器的代碼頁或排序方式如何，擴展存儲過程的名稱總是區分大小寫。

用戶可以執行在另一資料庫中創建的過程，只要該用戶擁有此過程或有在該資料庫中執行它的適當的許可權。用戶可以在另一台運行 microsoft® sql server™ 的伺服器上執行過程，只要該用戶有適當的許可權使用該伺服器（遠程訪問），並能在資料庫中執行該過程。如果指定了伺服器名稱但沒有指定資料庫名稱，sql server 會在用戶默認的資料庫中尋找該過程。

;number

是可選的整數，用於將相同名稱的過程進行組合，使得它們可以用一句 drop procedure 語句除去。該參數不能用於擴展存儲過程。

在同一應用程序中使用的過程一般都以該方式組合。例如，在訂購應用程序中使用的過程可以 orderproc;1、orderproc;2 等來命名。drop procedure orderproc 語句將除去整個組。在對過程分組後，不能除去組中的單個過程。例如，drop procedure orderproc;2 是不允許的。有關過程組的更多信息，請參見 create procedure。

@procere_name_var

是局部定義變數名，代表存儲過程名稱。

@parameter

是過程參數，在 create procedure 語句中定義。參數名稱前必須加上符號 (@)。在以 @parameter_name = value 格式使用時，參數名稱和常量不一定按照 create procedure 語句中定義的順序出現。但是，如果有一個參數使用 @parameter_name = value 格式，則其它所有參數都必須使用這種格式。

默認情況下，參數可為空。如果傳遞 null 參數值，且該參數用於 create 或 alter table 語句中不允許為 null 的列（例如，插入至不允許為 null 的列），sql server 就會報錯。為避免將 null 參數值傳遞給不允許為 null 的列，可以在過程中添加程序設計邏輯或採用默認值（使用 create 或 alter table 語句中的 default 關鍵字）。

value

是過程中參數的值。如果參數名稱沒有指定，參數值必須以 create procedure 語句中定義的順序給出。

如果參數值是一個對象名稱、字元串或通過資料庫名稱或所有者名稱進行限制，則整個名稱必須用單引號括起來。如果參數值是一個關鍵字，則該關鍵字必須用雙引號括起來。

如果在 create procedure 語句中定義了默認值，用戶執行該過程時可以不必指定參數。如果該過程使用了帶 like 關鍵字的參數名稱，則默認值必須是常量，並且可以包含 %、_、[ ] 及 [^] 通配符。

默認值也可以為 null。通常，過程定義會指定當參數值為 null 時應該執行的操作。

@variable

是用來保存參數或者返回參數的變數。

output

指定存儲過程必須返回一個參數。該存儲過程的匹配參數也必須由關鍵字 output 創建。使用游標變數作參數時使用該關鍵字。

如果使用 output 參數，目的是在調用批處理或過程的其它語句中使用其返回值，則參數值必須作為變數傳遞（即 @parameter = @variable）。如果一個參數在 create procedure 語句中不是定義為 output 參數，則對該參數指定 output 的過程不能執行。不能使用 output 將常量傳遞給存儲過程；返回參數需要變數名稱。在執行過程之前，必須聲明變數的數據類型並賦值。返回參數可以是 text 或 image 數據類型以外的任意數據類型。

default

根據過程的定義，提供參數的默認值。當過程需要的參數值沒有事先定義好的默認值，或缺少參數，或指定了 default 關鍵字，就會出錯。

n

是佔位符，表示在它前面的項目可以多次重復執行。例如，execute 語句可以指定一個或者多個 @parameter、value 或 @variable。

with recompile

強制編譯新的計劃。如果所提供的參數為非典型參數或者數據有很大的改變，使用該選項。在以後的程序執行中使用更改過的計劃。該選項不能用於擴展存儲過程。建議盡量少使用該選項，因為它消耗較多系統資源。

@string_variable

是局部變數的名稱。@string_variable 可以是 char、varchar、nchar 或 nvarchar 數據類型，最大值為伺服器的可用內存。如果字元串長度超過 4,000 個字元，則把多個局部變數串聯起來用於 execute 字元串。有關系統提供的 sql server 數據類型更多的信息，請參見數據類型。

[n]'tsql_string'

是一個常量，tsql_string 可以是 nvarchar 或 varchar 數據類型。如果包含 n，則該字元串將解釋為 nvarchar 數據類型，最大值為伺服器的可用內存。如果字元串長度超過 4,000 個字元，則把多個局部變數串聯起來用於 execute 字元串。

注釋
如果過程名稱的前三個字元為 sp_，sql server 會在 master 資料庫中尋找該過程。如果沒能找到合法的過程名稱，sql server 會尋找所有者名稱為 dbo 的過程。若要將存儲過程名稱解析為與系統存儲過程同名的用戶定義存儲過程，請提供一個完全合法的過程名稱。

參數可以通過利用 value 或 @parameter_name = value 來提供。參數不是事務的一個部分；因而如果事務中的參數值更改，且該事務在以後回滾，該參數值不會退回到以前的值。返回給調用方的值總是過程返回時的值。

當一個存儲過程調用另一個存儲過程時，會產生嵌套。當調用的過程開始執行時，嵌套級會增加，當調用過程執行結束時，嵌套級則會減少。嵌套級最高為32級，超過32級時，會導致整個調用過程鏈失敗。當前的嵌套級存儲在 @@nestlevel 函數中。

sql server 目前使用返回值 0 到 -14 來表示存儲過程的執行狀態。值 –15 到 -99 留作後用。有關保留的返回狀態值的列表的更多信息，請參見 return。

因為遠程存儲過程和擴展存儲過程不在事務的作用域中（除非在 begin distributed transaction 語句中發出或者是和不同的配置選項一起使用），所以通過調用執行的命令不能回滾。有關更多信息，請參見系統存儲過程和 begin distributed transaction。

當使用游標變數時，如果執行的過程傳遞一個分配有游標的游標變數，就會出錯。

在執行存儲過程時，如果語句是批處理中的第一個語句，則不一定要指定 execute 關鍵字。

使用帶字元串的 execute 命令
使用字元串串聯運算符 ( ) 為動態執行創建長字元串。每個字元串表達式可以是 unicode 與 non-unicode 數據類型的混合。

盡管每個 [n] 'tsql_string' 或 @string_variable 不得超過 8,000 個位元組，sql server 語法分析器中對這種串聯只進行邏輯處理而不佔用物理內存。例如，該語句決不會生成長 16,000 個串聯起來的字元串：

exec('name_of_8000_char_string' 'another_name_of_8000_char_string')

在 execute 語句執行前，不會編譯 execute 語句內的語句。

資料庫環境的更改只在 execute 語句結束前有效。例如，在這個例子的 exec 後，資料庫環境是 master：

use master exec ("use pubs") select * from authors

許可權
存儲過程的 execute 許可權默認給該存儲過程的所有者，該所有者可以將此許可權轉讓給其他用戶。當遇到 execute 語句時，即使 execute 語句是在存儲過程中，也會檢查在 execute 字元串內使用該語句的許可權。當運行一個執行字元串的存儲過程時，系統會在執行該過程的用戶環境中，而不是在創建該過程的用戶環境中檢查許可權。但是，如果某用戶擁有兩個存儲過程，並且第一個過程調用第二個過程，則在第二個過程中不進行 execute 許可權檢查。

示例
a. 使用 execute 傳遞單個參數
showind 存儲過程需要參數 (@tabname)，它是一個表的名稱。下面這個例子執行 showind 存儲過程，以 titles 為參數值。

說明 showind 存儲過程只是用來作為一個例子，pubs 資料庫並沒有此過程。

exec showind titles

在執行過程中變數可以顯式命名：

exec showind @tabname = titles

如果這是 isql 腳本或批處理中第一個語句，則 exec 語句可以省略：

showind titles

-或-

showind @tabname = titles

b. 使用多個參數與一個輸出參數
這個例子執行 roy_check 存儲過程，傳遞三個參數。第三個參數 @pc 是輸出參數。過程執行完後，返回變數可以從變數 @percent 得到。

說明 roy_check 存儲過程只是用作舉例，pubs 資料庫中並沒有此過程。

declare @percent int
execute roy_check 'bu1032', 1050, @pc = @percent output
set percent = @percent

c.使用帶一個變數的 execute 'tsql_string' 語句
這個例子顯示 execute 語句如何處理動態生成的、含有變數的字元串。這個例子創建 tables_cursor 游標來保存所有用戶定義表 (type = u) 的列表。

說明 此例子只用作舉例。

declare tables_cursor cursor
for
select name from sysobjects where type = 'u'
open tables_cursor
declare @tablename sysname
fetch next from tables_cursor into @tablename
while (@@fetch_status <> -1)
begin
/* a @@fetch_status of -2 means that the row has been deleted.
there is no need to test for this because this loop drops all
user-defined tables. */.
exec ('drop table ' @tablename)
fetch next from tables_cursor into @tablename
end
print 'all user-defined tables have been dropped from the database.'
deallocate tables_cursor

d.使用帶遠程存儲過程的 execute 語句
這個例子在遠程伺服器 sqlserver1 上執行 checkcontract 存儲過程，在 @retstat 中保存返回狀態，說明運行成功或失敗。

declare @retstat int
execute @retstat = sqlserver1.pubs.dbo.checkcontract '409-56-4008'

e. 使用帶擴展存儲過程的 execute 語句
下例使用 xp_cmdshell 擴展存儲過程列出文件擴展名為 .exe 的所有文件的目錄。

use master
execute xp_cmdshell 'dir *.exe'

f. 使用帶一個存儲過程變數的 execute 語句
這個例子創建一個代表存儲過程名稱的變數。

declare @proc_name varchar(30)
set @proc_name = 'sp_who'
exec @proc_name

g. 使用帶 default 的 execute 語句
這個例子創建了一個存儲過程，過程中第一個和第三個參數為默認值。當運行該過程時，如果調用時沒有傳遞值或者指定了默認值，這些默認值就會賦給第一個和第三個參數。注意 default 關鍵字有多種使用方法。

use pubs
if exists (select name from sysobjects
where name = 'proc_calculate_taxes' and type = 'p')
drop procedure proc_calculate_taxes
go
-- create the stored procere.
create procedure proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),
@p3 varchar(8) = 'car')
as
select *
from mytable

proc_calculate_taxes 存儲過程可以以多種組合方式執行：

execute proc_calculate_taxes @p2 = 'a'
execute proc_calculate_taxes 69, 'b'
execute proc_calculate_taxes 69, 'c', 'house'
execute proc_calculate_taxes @p1 = default, @p2 = 'd'
execute proc_calculate_taxes default, @p3 = 'local', @p2 = 'e'
execute proc_calculate_taxes 69, 'f', @p3 = default
execute proc_calculate_taxes 95, 'g', default
execute proc_calculate_taxes default, 'h', default
execute proc_calculate_taxes default, 'i', @p3 = default

e. 資料庫的exec是什麼意思，應該怎麼用呢

sqlserver中
exec命令有兩種用法，一種是執行一個存儲過程，另一種是執行一個動態的批處理

1.使用系統存儲過程sp_executesql運行unicode命令字元串
語法如下：
sp_executesql [@stmt=] stmt
[
{,[@params=] n'@parameter_name data_type [,...n]'}
{,[@param1=] 'value' [,...n]}
]
說明：必須先將各個包含所要運行的命令語句的unicode字元串相加在一起，再交給系統存儲過程sp_executesql來運行，而不能在sp_executesql的語句中來相加各個命令字元串。
舉例如下：
declare @dbname nvarchar(20),
@tbname nvarchar(20),
@sqlstring nvarchar(500)
set @dbname=n'northwind'
set @tbname=n'customers'
set @sqlstring=n'use ' @dbname char(13) --char(13)換行
set @sqlstring=@sqlstring n'select * from ' @tbname
--必須先將命令字元串組合完畢後再交給sp_executesql來運行
exec sp_executesql @sqlstring

2.使用execute命令運行命令字元串
要使用execute命令來運行一個命令字元串的語法如下：
exec[ute] ({@string_variable|[n] 'stql_string'} [ ...n])
從語法看出，可以先將包含所要運行的命令的字元串賦給一個局部變數@string_variable，再使用execute命令來運行，或是直接使用execute命令去運行一個包含所要運行的命令語句的字元串。此外，您也可以將多個包含所要運行的命令語句的字元串相加在一起，再交給execute命令來一次運行。

例：
declare @sql nvarchar(4000)
set @sql='select * from [表] where email=''[email protected]'''
exec (@sql)

f. sql中 exec （@s）的用法

一般exec(@s)代表執行一句動態sql。

可參考以下例子，如代碼：

declare@svarchar(2000)
declare@tablenamevarchar(20)
set@tablename='test'
set@s='select*from' @tablename ''
exec(@s)

結果：

注意：代碼中倒數第二句的寫法，也就是執行的動態sql語句，引用的動態名稱需要用引號及加號。最後一句的動態sql也要用括弧括起來。