SQL Server預存程序編程經驗技巧

下面我們討論的前提都是您有過一定的ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ預存程序的編寫經驗，下面的各種技巧如果沒有
特別註明，則同時適用於ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ７．０和ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ２０００。 

　　１、使用ＯＵＴＰＵＴ型別參數的預存程序的技巧 

一般的預存程序都是直接返回一段記錄集給調用者，但是有的時候我們只需要一些預存程序返回的一些參數的值，這時候
可以指定預存程序的ＯＵＴＰＵＴ參數，比如： 

　　Ｃｒｅａｔｅ　ｐｒｏｃｅｄｕｒｅ　ＧｅｔＮａｍｅ　 

　　＠ｕｉｄ　ｎｖａｒｃｈａｒ（１）， 

　　＠ｕｓｅｒｎａｍ　ｎｖａｒｃｈａｒ（１０）＝＇＇　ｏｕｔｐｕｔ 

　　ＡＳ 

　　ｓｅｔ　＠ｕｓｅｒｎａｍｅ＝＇ｈｏｎｇｃｈａｏ＇ 

　　ＧＯ 

　　在上面的預存程序當中我們傳遞進的參數是＠ｕｉｄ，而參數＠ｕｓｅｒｎａｍｅ則在調有的時候不需要傳遞進去。
這樣，預存程序會返回給我們參數＠ｕｓｅｒｎａｍｅ的值是‘ｈｏｎｇｃｈａｏ’。上面的都比較的容易，需要注意的
是，當在ＳＱＬ２０００中的時候，如果你的預存程序只有一個參數，並且這個參數是ＯＵＴＰＵＴ類型的，你必須在調
用這個預存程序的時候給這個參數一個初始的值，否則會出現調用錯誤的情況！ 

　　２、在預存程序中的書寫注意事項 

　　這一點在ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ７．０和ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ２０００種有些地方是不一樣的，也不
知道是不是微軟的遺漏，那就是有些系統的關鍵字在不同的版本之間有所不同，比如關鍵字ｌｅｖｅｌ，同樣的一句話： 

ｓｅｌｅｃｔ　＊　ｆｒｏｍ　ｕｓｅｒｓ　ｗｈｅｒｅ　ｌｅｖｅｌ＝１ 

在ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ７當中的預存程序當中運行沒有絲毫的問題，但是到了ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ２０
００當中則會出現啟動並執行錯誤，原因就是在於在ＭＳ　ＳＱＬ　Ｓｅｒｖｅｒ２０００中“ｌｅｖｅｌ”被當作了關鍵字
（奇怪的是ＳＱＬ７當中也同樣是關鍵字，卻沒問題），所以在ＳＱＬ２０００當中，上面的語句應當改為： 

ｓｅｌｅｃｔ　＊　ｆｒｏｍ　ｕｓｅｒｓ　ｗｈｅｒｅ　［ｌｅｖｅｌ］＝１ 

從上面的例子中我們可以看到，在你編寫預存程序的時候，最好在有可能和系統關鍵字的地方使用“［”和“］”將他包
圍起來，以避免在移植過程中出現的運行錯誤問題。 

　　３、在預存程序中使用系統預存程序ＳＰ＿Ｅｘｅｃｕｔｅｓｑｌ的注意事項 

　　我們在編寫自己的預存程序的時候，往往在很多的情況下，會使用到系統的預存程序ＳＰ＿Ｅｘｅｃｕｔｅ。但是需
要的注意的是，如果你在這個預存程序的參數（一般是一段ＳＱＬ語句）當中進行了臨時Ｔａｂｌｅ的操作，那末對於調
用者來說，這個臨時Ｔａｂｌｅ是不可見的，也就是說你無法通過臨時Ｔａｂｌｅ來在調用者和被調用者之間傳遞值。解
決的方法是使用全域臨時Ｔａｂｌｅ，也就是“＃＃”開頭的Ｔａｂｌｅ。 

　　４、在預存程序中使用臨時Ｔａｂｌｅ和遊標的注意事項 

　　如果我們的商業邏輯比較複雜，在預存程序當中，就需要一些媒介作為中轉檯，這時候暫存資料表舅發揮了作用，但是請
務必記得在使用完之後，即使刪除使用到的臨時Ｔａｂｌｅ。 

　　而在預存程序當中想要依次遍曆一個記錄集的唯一方法就是使用系統遊標，同樣要注意的是，在使用完成之後及時關
閉和銷毀遊標對象釋放他用到的資源。並且不在萬不得已的情況下，不要隨意使用遊標，因為他會佔用較多的系統資源，
尤其是對於大並發量的情況下，很容易使得系統資源耗盡而崩潰。 

　　使用臨時Ｔａｂｌｅ和遊標各有利弊，在使用的過程中要適當的利用即可！ 

　　５、在預存程序中調用外部的ＡｃｔｉｖｅＸ　ＤＬＬ程式 

　　有些特殊的情況下，我們可能會需要調用外部的ＡｃｔｉｖｅＸ　ＤＬＬ程式，這個時候就需要使用到系統的儲存過
程ｓｐ＿ＯＡＣｒｅａｔｅ以及其他的相關係統預存程序，都是以ｓｐ＿ＯＡ開頭的預存程序，可以自由的在自己的儲存
過程當中調用ＡｃｔｉｖｅＸ　ＤＬＬ的各種方法和屬性。比如下面的例子： 

　　ＤＥＣＬＡＲＥ　＠ｏｂｊｅｃｔ　ｉｎｔ 

　　ＤＥＣＬＡＲＥ　＠ｈｒ　ｉｎｔ 

　　ＤＥＣＬＡＲＥ　＠ｐｒｏｐｅｒｔｙ　ｖａｒｃｈａｒ（２５５） 

　　ＤＥＣＬＡＲＥ　＠ｒｅｔｕｒｎ　ｖａｒｃｈａｒ（２５５） 

　　ＤＥＣＬＡＲＥ　＠ｓｒｃ　ｖａｒｃｈａｒ（２５５），　＠ｄｅｓｃ　ｖａｒｃｈａｒ（２５５） 

　　－－　建立一個對象（ＳＱＬＤＭＯ．ＳＱＬＳｅｒｖｅｒ）． 

　　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＣｒｅａｔｅ　＇ＳＱＬＤＭＯ．ＳＱＬＳｅｒｖｅｒ＇，　＠ｏｂｊｅｃｔ　
ＯＵＴ 

　　ＩＦ　＠ｈｒ　＜＞　０ 

　　ＢＥＧＩＮ 

　　　　　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓｒｃ　ＯＵＴ，　＠ｄｅｓｃ　
ＯＵＴ　 

　　　　　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ），　Ｓｏｕｒｃｅ＝＠ｓｒ
ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ 

　　　　　　ＲＥＴＵＲＮ 

　　ＥＮＤ 

　　－－　設定對象的屬性． 

　　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＳｅｔＰｒｏｐｅｒｔｙ　＠ｏｂｊｅｃｔ，　＇ＨｏｓｔＮａｍｅ＇，　＇Ｇ
ｉｚｍｏ＇ 

　　ＩＦ　＠ｈｒ　＜＞　０ 

　　ＢＥＧＩＮ 

　　　　　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓｒｃ　ＯＵＴ，　＠ｄｅｓｃ　
ＯＵＴ　 

　　　　　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ），　Ｓｏｕｒｃｅ＝＠ｓｒ
ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ 

　　　　　　ＲＥＴＵＲＮ 

　　ＥＮＤ 

　　－－　通過ＯＵＴＰＵＴ參數擷取對象的屬性值． 

　　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＧｅｔＰｒｏｐｅｒｔｙ　＠ｏｂｊｅｃｔ，　＇ＨｏｓｔＮａｍｅ＇，　＠ｐ
ｒｏｐｅｒｔｙ　ＯＵＴ 

　　ＩＦ　＠ｈｒ　＜＞　０ 

　　ＢＥＧＩＮ 

　　　　　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓｒｃ　ＯＵＴ，　＠ｄｅｓｃ　
ＯＵＴ　 

　　　　　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ），　Ｓｏｕｒｃｅ＝＠ｓｒ
ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ 

　　　　　　ＲＥＴＵＲＮ 

　　ＥＮＤ 

　　ＰＲＩＮＴ　＠ｐｒｏｐｅｒｔｙ 

　　－－　調用對象的方法 

　　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＭｅｔｈｏｄ　＠ｏｂｊｅｃｔ，　＇Ｃｏｎｎｅｃｔ＇，　ＮＵＬＬ，　＇ｍ
ｙ＿ｓｅｒｖｅｒ＇，　＇ｍｙ＿ｌｏｇｉｎ＇，　＇ｍｙ＿ｐａｓｓｗｏｒｄ＇ 

　　ＩＦ　＠ｈｒ　＜＞　０ 

　　ＢＥＧＩＮ 

　　　　　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓｒｃ　ＯＵＴ，　＠ｄｅｓｃ　
ＯＵＴ　 

　　　　　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ），　Ｓｏｕｒｃｅ＝＠ｓｒ
ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ 

　　　　　　ＲＥＴＵＲＮ 

　　ＥＮＤ 

　　－－　銷毀已經建立的ＡｃｔｉｖｅＸ對象 

　　ＥＸＥＣ　＠ｈｒ　＝　ｓｐ＿ＯＡＤｅｓｔｒｏｙ　＠ｏｂｊｅｃｔ 

　　ＩＦ　＠ｈｒ　＜＞　０ 

　　ＢＥＧＩＮ 

　　　　　ＥＸＥＣ　ｓｐ＿ＯＡＧｅｔＥｒｒｏｒＩｎｆｏ　＠ｏｂｊｅｃｔ，　＠ｓｒｃ　ＯＵＴ，　＠ｄｅｓｃ　
ＯＵＴ　 

　　　　　ＳＥＬＥＣＴ　ｈｒ＝ｃｏｎｖｅｒｔ（ｖａｒｂｉｎａｒｙ（４），＠ｈｒ），　Ｓｏｕｒｃｅ＝＠ｓｒ
ｃ，　Ｄｅｓｃｒｉｐｔｉｏｎ＝＠ｄｅｓｃ 

　　　　　　ＲＥＴＵＲＮ 

　　ＥＮＤ 

　　６、在預存程序中使用資料庫交易處理 

　　在很多的情況下，我們在預存程序中都會遇到需要同時操作多個表的情況，這時候就需要避免在操作的過程中由於以
外而造成的資料的不一致性。這時候就需要將操作多個表的操作放入到事務中進行處理。 

　　但是需要注意的是，不能在事務中使用return語句強行退出，這樣會引發事務的非正常錯誤，不能保證資料的一致
性。 

　　並且，一旦將多個處理放入事務當中，系統的處理速度會有所降低，所以應當將頻繁操作的多個可分割的處理過程放
入到多個預存程序當中，這樣會大大提高系統的響應速度，但是前提是不違背資料的一致性。 

　　看完了上面的這些編寫SQL Server預存程序當中的技巧，相信對您或多或少會有些協助，也希望通過上面的一些經驗
總結，可以使得您在應用SQL Server預存程序的時候，有意識的可以避免一些彎路。