網路工作室暑假後第二次培訓資料（SQLServer預存程序和ADO.NET訪問預存程序）整理（一）

因為昨天我的SQL Server 2008資料庫在使用的時候突然就打不開了。而起前天還用它來寫T-SQL語句來著。所以很是鬱悶啊，到網上查詢以後，不能解決，就乾脆重新安裝了一邊，所以昨天整理的內容不能夠及時的更新到部落格上，只能今天繼續整理，然後更新了。

這次主要是講ADO.NET訪問預存程序的知識，所以，在開篇的時候還是把SQL Server預存程序的知識給串講，複習一下。

對於前段程式開發人員來說，預存程序是很重要的工具，他可以使開發人員在不瞭解表架構的情況下使用預存程序對資料進行處理。同時，由於預存程序具有封裝的概念，當邏輯規則改變時，可以在不影響前端程式設計的情況下對後端程式進行修改。

預存程序包括視圖的所有的優點，視圖只有select命令，而預存程序則包含了幾乎所有的T-Sql命令。
預存程序可以很大的提高T-SQl命令的執行效率。
T-Sql語句在SQl Server 用戶端執行的過程：

每次執行時都需要分析語句正確與否，最佳化與編譯成執行計畫，再對照SQl Server中是否有相同的執行計畫。

一般的T-SQl命令重複執行時，會一直執行同樣的分析，最佳化，編譯的工作，將耗費大量的CPU時間。

而是用預存程序則是只進行一次分析，最佳化，編譯的過程，之後就直接依照SqlServer緩衝中的執行計畫直接執行。

優點：減少網路頻寬的佔用：因為預存程序是在SqlServer端執行，不需要和前端進行來回的溝通，相比之下就減少了在網路上傳遞包的頻寬佔用量，對於網路程式執行來說，使用預存程序能夠減少對網路頻寬的佔用。

SqlServer預存程序的聲明方式：

Create  proc預存程序名稱

As

T-sql命令

SqlServer預存程序分為無參數預存程序和有參數預存程序

1）沒有參數的預存程序

調用預存程序

Exec  預存程序名稱；

這樣的用法和視圖差不多，但是視圖不可以使用order by命令。而預存程序沒有這樣的限制。

樣本1-1代碼如下：

1 --建立不帶參數的預存程序2 create proc select_activity3 as4 --該預存程序所執行的sql命令5 select * from T_Activity;

SQL調用該預存程序

1 --調用select_activity預存程序2 exec select_activity

有參數的預存程序

主要有三種類型的參數：

1）輸入參數2）輸出參數3）傳回值

輸入參數即在調用這個預存程序之前必須輸入參數而該參數僅在程式中使用，並不會再返回給使用者

基本的聲明方式：

Create proc  預存程序名稱

@輸入參數名稱1  資料類型，

@輸入參數名稱2  資料類型

As

T-sql命令

如果存在多個參數，就需要在各個輸入參數間以逗號分隔 。如果參數是字串或者日期，則需要用單引號括起來，而數字則不用。

備忘：

預存程序通常不要編寫太長，因為預存程序是在資料庫端執行的，太長的話會影響執行效率；

樣本1-2代碼如下：

 1 --建立帶參數的預存程序 2 create proc select_activity1 3  4 --修改預存程序的語句 5 --alter proc select_activity1 6  7 --聲明參數，多個參數有“,”號分隔字元隔開，參數後面聲明該參數的資料類型 8 @id int, 9 @activityName nvarchar(50)10 as11 --該預存程序所執行的Sql命令12 select * from T_Activity where id=@id and activityName=@activityName;13 14 --調用帶參數的預存程序15 --如果參數是字串或者日期，則需要用單引號括起來，而數字則不用。16 exec select_activity1 5,'比武招親'17 18 --或者19 exec select_activity120 @id=1,21 @activityName='比武招新';

預存程序中可以包含一些簡單的編程語句

比如：if ，begin  ·····end····等語句

樣本1-3代碼如下：

 1 --在設定參數時，如果需要給設定的參數賦值，則在聲明的參數後面加“=”後面跟所設定的初始值，這裡設定的初始值為空白 2 create proc sele_activity 3 @id int=null, 4 @activityInfoId int=null 5 as 6  7 --使用的if語句來判斷輸入的參數值是否為空白，如果為空白則返回前端錯誤資訊“請輸入參數”，而且使用return立刻結束該預存程序 8 if @id is null or @activityInfoId is null 9     begin10         raiserror('請輸入參數',16,1)11         return12         end13 --執行的sql命令14 select * from T_Activity where id=@id or activityInfoId=@activityInfoId;15 16 --使用了沒有給參數賦值的預存程序，返回錯誤17 --錯誤資訊：“訊息 50000，層級 16，狀態 1，過程 sele_activity，第 7 行 請輸入參數”18 exec sele_activity;19 20 --這樣調用返回正確的結果21 exec sele_activity22 @id=2,23 @activityInfoId=8;

輸出參數的預存程序

輸出參數和輸入參數剛好相反，輸入參數只會在as一下活動，而輸出參數則返回前端使用

輸出參數的聲明過程：

Createproc預存程序名稱

@輸入參數名稱   資料類型

@輸出參數名稱   資料類型  output

As

T-Sql命令

在聲明輸出參數變數時，必須聲明為output，預存程序執行完成以後會將輸出參數返回前端

樣本1-4代碼如下：

 1 --建立select_MathResult的預存程序 2 create proc select_MathResult 3 --聲明輸出參數@activityName和參數的類型 4 @activityName nvarchar(50), 5 --聲明輸出參數,並使用output來表明該參數為輸出參數 6 @result int output 7  8 as 9 --使用T-SQL語句來返回相同activityName的資料行數10 select @result=count(*) from T_Activity where activityName=@activityName11 12 --調用預存程序，調用時先聲明接受輸出參數的參數@answer和該參數的類型13 declare @answer int14 exec select_MathResult '比武招親' ,@answer output15 16 select '結果是',@answer

這樣就完成了對Sql Server預存程序的複習，在下一篇文章將要介紹怎樣使用ADO.NET訪問預存程序。文章地址：http://www.cnblogs.com/qq731109249/archive/2012/10/07/2714238.html