No Money No Honey

一直都不知到啥是預存程序，後來大概知道它是做什麼用的，直到現在才開始面對它，大概了解一下它怎麼用。

出處:http://yes.nctu.edu.tw/SQL/StoredProcedure/StoredProcedure.htm

撰寫 Stored Procedures

利用 ADO 來存取資料庫是一件既輕鬆又簡單的工作, 不過有的時候又臭又長的 SQL 敘述實在很煩人, 這篇文章將介紹如何利用 Stored Procedure 來簡化你的 ASP 程式

本質上 Stored procedures (有時又稱 Sproc) 就是儲存在資料庫中的 Script 或 Batch, 撰寫 Sproc 並不是太難的一件事, 通常你會這樣查詢資料庫

Select * From Table

如何將它變成 Sproc? 以下是簡單的例子

Create Proc sp_SprocName1
As
  Select * From Table
Go

現在 Sproc 已成功的建立, 你可以試著去執行它

Exec sp_SprocName1

你可以為你的 Sproc 取任何的名字, 在 SQL 7.0 的命名原則以 128 個字元為限, SQL 並不規定 Stores procedure 之命名要以 sp_ 開頭, 但強烈建議你這麼做!

接下來我們再做些例子, 例如在查詢中加入 where 子句, Select * From Table Where Column1 = 0 , 這裡你是不是會覺得 Column1 的值如果可以改為一個輸入參數由我們輸入, 那就更加完美!

Create Proc sp_SprocName2
@MyInput int

預設資料庫看起來沒什麼，但是如果您剛好把預設資料庫離線…………那好笑的事就發生了，你會無法再使用SQL Server Enterprise Manager登入，會出現類似 無法開啟使用者預設資料庫。登入失敗。的訊息，要再讓剛離線的資料庫上線也不行，阿程式也不會自動詢問或是自動幫您改成其它預設資料庫，只能說  掯~~~微”軟” 的SQL2005 實在有夠笨…………………

問題出現了還是要解決，

登入時先選別的資料庫

SELECT declno as no
FROM decldatah 
union all 
SELECT declno
FROM decldatab 

看科學家研究，人會覺得走在左邊的人比較有男(女)人味，因此想泡妞的請走在女伴的左邊……………不是沒有道理的，台灣屬於靠右邊走的習慣，如果男士在女生左邊，會有保護女生讓女生走比較路邊比較安全的感覺............................再說連電腦SQL語法也是要你在左邊呢!!!!

昨天去書店剛好看到有介紹Join的書翻了一下，發現 left join 正符合我的需求，試了三個Table的 left Join一次把我要的都列表出來，省了另外兩個額外查詢，看文獻也說使用join比較有效率。

select g.*, cbas.custid, cbas.cmpabv, cntc.cntcid, cntc.phone from dbo.gdsinfomt g
left join dbo.custbas cbas on (g.Agent = cbas.custid)
left join dbo.custcntc cntc on ((g.Agent = cntc.custid) and (cntc.cntcid = substring(g.declno,1,2)))
where g.senddate >= '20100128'
and g.senddate <= '20100128'

SQL與法中直接擷取字串的函式使用 substring(原字串, 啟始取值位置, 欲取的字元數)

這裡與DELPHI的Copy用法一摸摸一樣樣~

範例：取g.declno前兩字元

cntc.cntcid = substring(g.declno,1,2)

文章出處：http://www.dotblogs.com.tw/jimmyyu/archive/2009/07/03/9088.aspx

這是一個曾經發生過的案例，兩年前我在客戶家處理一個效能的案子時，客戶告知：你們的系統最近跑起來比過去慢了不少，每個動作平均大約慢了2-3秒鐘，我心想：我們最近不會改了什麼東西吧。

本著追根究柢的精神，開始跟客戶喇勒：最近有人做什麼嗎？有人版更嗎？DBA有改設定嗎？MIS有做系統調整嗎？這一連串的閒聊問答，最後得到的結果很好：沒有....

根據我在客戶家處理多年問題的經驗，客戶有這種回答也是很合理的，但我直覺問題並不單純，就開始進行問題的測試，從一些現象上看起來，最後把問題轉到DB上去了，最後找到DB中有個設定被修改了，這個設定叫作Auto Close(自動關閉)，詳細的內容可以看這個連結，講的蠻清楚的：http://sharedderrick.blogspot.com/2009/02/autoclose.html

針對這個設定有個地方要特別注意，在SQL Server 2000以前，Create database時，這個值預設是ON；SQL Server 2005以上的版本預設值則是OFF。

http://sharedderrick.blogspot.com/2009/02/autoclose.html

=============================================================================================================認識 SQL Server Database 資料庫選項：AUTO_CLOSE(自動關閉)

資料庫 AUTO_CLOSE 選項

選項值
說明

ON(True)

當設定為：ON 時，資料庫會完全關閉，並在最後一個使用者結束之後釋放其資源。當使用者試圖重新使用資料庫時，會自動重新開啟資料庫。例如，藉由發出 USE database_name 陳述式。

這個選項可能會造成經常存取之資料庫的效能降低，因為在每一個連接之後都會增加開啟和關閉資料庫的負擔。
而且在每一個連接之後，啟用此選項也會排清程序快取。

對於桌面資料庫而言，啟用此選項非常有用，因為它可讓您將資料庫檔案當做一般檔案來管理。您可以移動它們、複製它們來建立備份，甚至可以用電子郵件將它們傳給其他使用者。

在舊版 SQL Server 中，AUTO_CLOSE 是同步處理序，當有重複建立和中斷與 Database Engine 之連接的應用程式存取資料庫時，該處理序可能會降低效能。從 SQL Server 2005 開始，AUTO_CLOSE 處理序是非同步的；重複開啟和關閉資料庫不再降低效能。

當啟用此選項時，sys.databases 目錄檢視中的某些資料行及 DATABASEPROPERTYEX 函數會傳回 NULL，因為資料庫無法擷取資料。若要解決這個問題，請執行 USE 陳述式來開啟資料庫。

此啟用使選項也會造成，當資料庫關閉的作業執行時，會自動清除 SQL Server 執行個體的計畫快取。

若清除計畫快取會導致重新編譯所有後續執行計畫，而且可能會導致查詢效能突然暫時下降。在 SQL Server 2005 Service Pack 2 中，針對每次清除計畫快取的快取存放區，SQL Server 錯誤記錄檔會包含下列參考訊息：「由於某些資料庫維護或重新設定作業，SQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清。」只要快取發生排清，這個訊息就會每五分鐘記錄一次。

OFF(False)

當設定為：OFF 時，則在最後一個使用者結束之後資料庫仍為開啟。
資料庫鏡像需要 AUTO_CLOSE OFF。

預設值：