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