延續昨天討論的主題：Store Procedure 的除錯技巧

1.PRINR 指令(或是 select ) ，這是最基本的除錯指令
2.將有問題的程式片段 copy 到 Database4 中，mark 部分程式重複執行
3.寫到 log 暫存檔
4.查看錯誤訊息紀錄

PRINR 指令(或是 select ) ，這是最基本的除錯指令

不知道從甚麼時候開始，很多程式入門的書籍的第一個範例程式，都是著名的 Hello World

10 PRINT 'Hello World'
20 GOTO 10

所以，我們今天要介紹的第一個 Store Procedure 就是在程式中顯示某些變數或是欄位的值
MS-SQL

print convert(varchar(20),getdate(),112);
select convert(varchar(20),getdate(),112);

MariaDB 沒有 print，而是直接用 select 指令

SELECT 'some content'

這是最基本的除錯方式，每個 Developer 都會用，所以我就不囉嗦了。

將有問題的程式片段 copy 到 Database4 中，mark 部分程式重複執行

通常寫 Store Procedure 時，發生的錯誤可以分為兩大類

1.語法錯誤
2.邏輯錯誤

1.語法錯誤
如果是語法錯誤，通常在 create 或 alter 程式時，就會報錯，也會顯示相關的錯誤訊息，這和一般的 Compiler 是很類似的。這一類的問題，錯誤通常發生是對語法的不熟悉，通常不難解決，如果錯誤訊息看不懂，有時我會直接將錯誤訊息問 google 大神，根據經驗，通常都會找到錯誤的原因。下面舉個簡單的範例(程式片段)

  select *,length(pname) from zen_customer 
  where pid in (select col from dbo.udf_split(@pid,',')) 
  and deliveraddress like '%' + @addr + '%'

錯誤的原因說得很清楚，沒有 length() 這個函數，改用 len() 就可以了。

2.邏輯錯誤
如上所述，語法錯誤通常不難解決，SQL 的難以 debug 通常是邏輯問題，而非語法問題，也就是取得的資料或是數字結果和預期不符。這個部分真要認真說起來，天就會黑一半。說實話，還真的沒有銀彈可以解。我只能作一些經驗上的分享。

1.對 Table Schema 要完全透徹了解
2.對可能有 null 值欄位的任何判斷或取值、計算等，都要防範錯誤發生
3.對任何 join 語法，要特別注意 on 的條件，要考慮 join 是否會取不到資料，或是資料重複
4.豐富的 debug 經驗
5.直覺

3.寫到 log 暫存檔

就像一般的程式語言，有些錯誤 Compiler 是無法發現，而是在執行時才會發生(這通常和資料內容有關，語法和邏輯事實上都沒有錯誤)。

但是我們寫程式時，當然不希望看到由 SQL Enginer 所發出的令人看不懂的錯誤訊息，所以我們通常會利用 try catch 將可能會報錯的程式包起來。可是如果沒有將錯誤記錄起來，資料內容就會錯亂，所以通常我們會寫到 log 檔紀錄，而在執行完後，將錯誤的內容顯示出來。

當然，除了錯誤 log，有時我們也會記錄執行過程中的資訊，因為有時錯誤只會發生在某些特定情況下(例如某產品 item 或某客戶)，所以只好將計算過程也一併寫到 log，然後用手算的方式，追蹤可能的錯誤原因。相信我，邏輯性的錯誤，通常是靠這種笨方法查出來的。sa 在查帳時，必須有這一種 log，否則有些問題是查不出來的。

begin try
  sql statement
end try
  begin catch
    --if error, write to log 
	print ERROR_MESSAGE();
	insert into error_log (LOGINID,UNIQUENO,ONHANDDATE,BILLNO) values
      (@LOGINID,@opouniqueno,@BILLDATE,@BILLNO)
  end catch

4.查看錯誤訊息紀錄

上面談的是批次處理資料時，要將問題資料記錄到 log，方便追蹤錯誤發生。
而下面的語法，通常是用在 api 回傳 json 時配合使用。這是最後一段錯誤訊息的防錯機制，就算是發生的嚴重的錯誤，也要讓呼叫端不受影響，能繼續執行下去。

sql statement ...
begin try
    execute sp_executesql @sql;
	select 0 code,'成功' msg,0 recno;
end try
begin catch
  -- Execute the error retrieval routine.
  -- 或是也可 SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
  print @sql
  execute usp_GetErrorInfo_v2;
end catch

CREATE PROCEDURE usp_GetErrorInfo_v2
/*
  https://technet.microsoft.com/zh-tw/library/ms179296(v=sql.105).aspx
  傳回錯誤訊息, 通常配合 begin catch 中使用
*/
AS
    SELECT 
        ERROR_NUMBER() AS code,        
        ERROR_MESSAGE() as msg,
		0 as recno;

今天這篇文章是本系統文耗時最久的一章，除錯本來就是最難掌握的技巧。跟個人經驗有直接的關係，所以很難用簡單的文字說明清楚，只能就大原則說明。其他更重要的，還是經驗的累積。今天就寫到這，感謝您的收看，明天見。