執(zhí)行存儲過程
直接執(zhí)行存儲過程可以使用EXECUTE命令來執(zhí)行，其語法形式如下：
[[EXEC[UTE]]
? ?{? ?? ? [@return_status=]
? ?? ?? ? {procedure_name[;number]|@procedure_name_var}? ?? ?? ?? ?[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}? ?? ?
? ?? ?[,...n]
[ WITH RECOMPILE ]
使用 EXECUTE 命令傳遞單個參數(shù)，它執(zhí)行 showind 存儲過程，以 titles 為參數(shù)值。showind 存儲過程需要參數(shù) (@tabname)，它是一個表的名稱。其程序清單如下：
? ? EXEC showind titles
當然，在執(zhí)行過程中變量可以顯式命名：
? ? EXEC showind @tabname = titles
如果這是 isql 腳本或批處理中第一個語句，則 EXEC 語句可以省略：
? ? showind titles或者showind @tabname = titles
下面的例子使用了默認參數(shù)
USE Northwind
GO
CREATE PROCEDURE insert_Products_1
? ? ? ? ( @SupplierID_2 ? ? ? ? int,
? ? ? ???@CategoryID_3 ? ? ? ? int,
? ?? ?? ?? ?? ???@ProductName_1 nvarchar(40)='無')
AS INSERT INTO Products
? ? ? ???(ProductName,SupplierID,CategoryID)
VALUES
? ? ? ? (@ProductName_1,@SupplierID_2,@CategoryID_3)
GO
exec insert_Products_1 1,1
Select * from Products where SupplierID=1 and CategoryID=1
GO
下面的例子使用了返回參數(shù)
USE Northwind
GO
CREATE PROCEDURE query_products
(? ?? ?@SupplierID_1 int,
? ?? ???@ProductName_2 nvarchar(40) output)
AS
select @ProductName_2 = ProductName? ?from products
where SupplierID = @SupplierID_1
執(zhí)行該存儲過程來查詢SupplierID為1的產(chǎn)品名：
declare @product nvarchar(40)
exec query_products 1,@product output
select '產(chǎn)品名'= @product
go
查看存儲過程
? ?存儲過程被創(chuàng)建之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中。可以使用使用企業(yè)管理器或系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程。
使用企業(yè)管理器查看用戶創(chuàng)建的存儲過程
? ?在企業(yè)管理器中，打開指定的服務器和數(shù)據(jù)庫項，選擇要創(chuàng)建存儲過程的數(shù)據(jù)庫，單擊存儲過程文件夾，此時在右邊的頁框中顯示該數(shù)據(jù)庫的所有存儲過程。用右鍵單擊要查看的存儲過程，從彈出的快捷菜單中選擇屬性選項，此時便可以看到存儲過程的源代碼。
使用系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程
可供使用的系統(tǒng)存儲過程及其語法形式如下：
sp_help：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型
? ?sp_help [[@objname=] name]
參數(shù)name為要查看的存儲過程的名稱。
? ?sp_helptext：用于顯示存儲過程的源代碼
? ?sp_helptext [[@objname=] name]
參數(shù)name為要查看的存儲過程的名稱。
? ?sp_depends：用于顯示和存儲過程相關的數(shù)據(jù)庫對象
? ?sp_depends [@objname=]’object’
參數(shù)object為要查看依賴關系的存儲過程的名稱。
? ?sp_stored_procedures：用于返回當前數(shù)據(jù)庫中的存儲過程列表
修改存儲過程
? ? 存儲過程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTER PROCEDURE語句可以更改先前通過執(zhí)行 CREATE PROCEDURE 語句創(chuàng)建的過程，但不會更改權限，也不影響相關的存儲過程或觸發(fā)器。其語法形式如下：
? ?ALTERPROC[EDURE]procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]][,...n] [WITH
? ?{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS? ?
sql_statement [ ...n ]
重命名和刪除存儲過程
1. 重命名存儲過程
??修改存儲過程的名稱可以使用系統(tǒng)存儲過程sp_rename，其語法形式如下：
? ?? ?sp_rename??原存儲過程名稱，新存儲過程名稱
? ?? ?另外，通過企業(yè)管理器也可以修改存儲過程的名稱。
刪除存儲過程
? ?刪除存儲過程可以使用DROP命令，DROP命令可以將一個或者多個存儲過程或者存儲過程組從當前數(shù)據(jù)庫中刪除，其語法形式如下：
? ?? ? drop procedure {procedure} [,…n]
當然，利用企業(yè)管理器也可以很方便地刪除存儲過程。
存儲過程的重新編譯
? ?在我們使用了一次存儲過程后，可能會因為某些原因，必須向表中新增加數(shù)據(jù)列或者為表新添加索引，從而改變了數(shù)據(jù)庫的邏輯結(jié)構(gòu)。這時，需要對存儲過程進行重新編譯，SQL Server提供三種重新編譯存儲過程的方法 ：
? ? 1、在建立存儲過程時設定重新編譯
? ?? ? 語法格式：CREATE??PROCEDURE? ?procedure_name? ? WITH? ?RECOMPILE? ? AS? ?sql_statement
? ???2、在執(zhí)行存儲過程時設定重編譯
? ?? ? 語法格式： EXECUTE??procedure_name??WITH??RECOMPILE
? ? 3、通過使用系統(tǒng)存儲過程設定重編譯
? ?? ???語法格式為：??EXEC??sp_recompile??OBJECT
系統(tǒng)存儲過程與擴展存儲過程
1.系統(tǒng)存儲過程
? ?? ?? ???系統(tǒng)存儲過程存儲在master數(shù)據(jù)庫中，并以sp_為前綴，主要用來從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理SQL Server提供幫助，為用戶查看數(shù)據(jù)庫對象提供方便。比如用來查看數(shù)據(jù)庫對象信息的系統(tǒng)存儲過程sp_help、顯示存儲過程和其它對象的文本的存儲過程sp_helptext等。
2.擴展存儲過程：
? ?? ?? ? 擴展存儲過程以xp_為前綴，它是關系數(shù)據(jù)庫引擎的開放式數(shù)據(jù)服務層的一部分，其可以使用戶在動態(tài)鏈接庫(DLL)文件所包含的函數(shù)中實現(xiàn)邏輯，從而擴展了Transact-SQL的功能，并且可以象調(diào)用Transact-SQL過程那樣從Transact-SQL語句調(diào)用這些函數(shù)。
? ?? ?例:??利用擴展存儲過程xp_cmdshell為一個操作系統(tǒng)外殼執(zhí)行指定命令串，并作為文本返回任何輸出。
? ?? ?執(zhí)行代碼：
? ?? ?? ?use master
? ?? ?? ? exec xp_cmdshell 'dir *.exe'? ?
? ?? ? 執(zhí)行結(jié)果返回系統(tǒng)目錄下的文件內(nèi)容文本信息。
最后給大家舉一個例子：
/**
1、? ? ? ? 在Northwind數(shù)據(jù)庫中，創(chuàng)建一個帶查詢參數(shù)的存儲過程，
要求在輸入一個定購金額總額＠total時，查詢超出該值的所
有產(chǎn)品的相關信息，包括產(chǎn)品名稱和供應商名稱、單位數(shù)量、
單價、以及該產(chǎn)品的定購金額總額，并通過一個輸出參數(shù)返回
滿足查詢條件的產(chǎn)品數(shù)
**/
IF exists (select * from SysObjects where name='more_than_total' and type='p')
? ?drop procedure more_than_total
go
CREATE PROCEDURE More_Than_Total
? ? ? ? @total money = 0
AS
Declare @amount smallint
BEGIN
? ? ? ? select distinct
? ?? ?? ???P.productName,
? ?? ?? ???S.contactName,
? ?? ?? ???P.UnitPrice
? ?? ?? ???
? ? from Products P inner join [order Details] O
? ?? ?? ?on p.productID=o.productID inner join suppliers s
? ?? ?? ?on p.supplierID=s.SupplierID
? ? where O.productID in
? ? (select productID
? ???from? ?[order Details]
? ???group by productId
? ???having sum(quantity*unitprice)>@total
? ? )
END
GO