SQL語法提示工具SQL Prompt教程：忽略使用或濫用RETURN關鍵字（BP016）

SQL Prompt是一款實用的SQL語法提示工具。SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據需要進行自定義，使之以預想的方式工作。

點擊下載SQL Prompt免費版

文章解釋了在存儲過程或批處理中正確使用RETURN關鍵字，將非零RETURN代碼傳遞給調用進程，并通知它錯誤，還解釋了一些誤用。

所有存儲過程、語句塊和批處理都返回一個記錄其執(zhí)行成功的代碼。如果批處理或過程到達結尾，它將自動返回0（零），這意味著成功，除非您使用RETURN關鍵字指定，否則使用整數參數。除了0之外，沒有為任何數字定義任何東西，但約定存在任何其他數字都表示某些描述失敗。如果發(fā)生錯誤，您應該捕獲返回的值并將其發(fā)送到調用進程，以便它可以相應地做出響應。

僅使用RETURN值來傳達流程的成功或失敗，永遠不要將值作為流程的一部分返回，例如某一天的購買數量。此外，存儲過程或批處理應該永遠不會有沒有值的RETURN關鍵字，如果SQL Prompt 檢測到此錯誤，它將發(fā)出BP016警告。

什么是RETURN關鍵字，它返回什么？

要從報表任何問題的任何存儲過程或批處理返回值，您需要將其分配給變量并使用RETURNcontrol-of-flow關鍵字，并將值作為參數。此RETURN將立即終止批處理的執(zhí)行，并返回您作為參數傳遞的值。如果嘗試使用RETURN關鍵字從存儲過程返回NULL，則會收到警告，并返回0。如果某個過程遇到需要立即終止的錯誤，它將返回NULL，因為它永遠不會到達RETURN關鍵字或批處理的末尾！如果批處理或過程到達結尾，它將自動返回零。

某些系統存儲過程會返回運行批處理時發(fā)生的實際錯誤代碼，包括程序中RAISERROR語句中指定的那些代碼，但是沒有標準表明您需要執(zhí)行此操作。實際上，文檔建議您可以根據執(zhí)行過程的錯誤將任意值傳遞回調用批處理。

我們可以證明，即使sp_ExecuteSQL返回由錯誤生成的錯誤代碼，也會返回0，如果它只是一個警告。如果它只是一個警告，它會繼續(xù)執(zhí)行，當然，如果它隨后成功則其返回代碼為0，如果不成功則返回失敗的錯誤代碼。

raiserror('HELP, I''m trapped in this batch!',5,1)
/*
Msg 50000, Level 5, State 1, Line 25
HELP, I'm trapped in this batch!
*/
DECLARE @Return int
EXECUTE @Return= sp_executeSQL  N'raiserror(''HELP, I''''m trapped in this batch!'',16,1)'
SELECT @Return
--returns 50000 (user-defined error). But what if we do a warning instead of an error?
DECLARE @Return int
EXECUTE @Return= sp_executeSQL  N'raiserror(''HELP, I''''m trapped in this batch!'',5,1)'
SELECT @Return
--returns 0 because it was only a warning
SELECT * FROM dbo.MissingTable
/*
Msg 208, Level 16, State 1, Line 40
Invalid object name 'dbo.MissingTable'.
*/
DECLARE @Return int
EXECUTE @Return= sp_executeSQL  N'SELECT * FROM dbo.MissingTable'
SELECT @Return
--returns 208

雖然我們大多數人日常編寫的簡單存儲過程不需要太多使用RETURN代碼，但是當我們開始執(zhí)行更復雜的基于事務的處理時，這樣做的價值很快就會出現。

讓我們從返回代表錯誤號的代碼開始。

CREATE PROCEDURE #TryoutProcedure
AS
  BEGIN
    BEGIN TRY
      SELECT 1 / 0; --deliberately trigger a divide by zero
    END TRY
    BEGIN CATCH
      RETURN Error_Number(); --return the error
    END CATCH;
  END;
GO
DECLARE @Return INT;
EXECUTE @Return = #TryoutProcedure; --execute our sample procedure
SELECT @Return
IF Coalesce(@Return,0) <> 0
  SELECT * FROM sys.messages --and see if the error was passed back
     WHERE message_id = @Return AND language_id = 1033;

您會注意到在程序結束時無需添加RETURN 0，因為這是自動完成的。如果到達批處理的末尾，SQL Server會認為您已贏了，因此返回0。如果您嘗試執(zhí)行...

SELECT * FROM dbo.MissingTable

…代替…

SELECT 1 / 0;

...你會發(fā)現返回NULL以及“無效的對象名”錯誤。為什么？它放棄了程序而不是遵守TRY…CATCH構造。這是因為我們在語句級重新編譯期間觸發(fā)了對象名稱解析錯誤（由于延遲名稱解析，所以存儲過程無故障編譯）。SQL Server無法從此錯誤中恢復批處理并使用a完全中止執(zhí)行NULL。正如福爾摩斯所說，這些都是深水。更確切地說：

• TRY…CATCH 不會捕獲嚴重性為10或更低的警告或信息性消息。

• TRY…CATCH只能在正在運行的過程中運行。這意味著，例如，無法捕獲嚴重性為20或更高的錯誤，這些錯誤會阻止會話的SQL Server數據庫引擎任務處理。這也適用于注意事項，例如客戶端中斷請求或客戶端連接中斷，以及系統管理員使用該KILL語句結束會話時。如果存在編譯錯誤（例如語法錯誤），這會阻止批處理運行，那么它將永遠不會到達TRY …CATCH語句。如果在任何重新編譯期間解析對象名稱時出錯，也會發(fā)生這種情況。

Code Smells和RETURN值

RETURN值只應用于表示所執(zhí)行操作的成功或失敗，以及其原因。但是，在OUTPUT參數之前有一段時間，該RETURN值是將任何類型的整數值傳遞回批處理的唯一簡單方法。

CREATE PROCEDURE #HowManylettersInWord
@AString nvarchar(2000)
AS
/* never do this. This is a code smell */
  BEGIN
  RETURN (PATINDEX('%[^-a-z]%',@AString+'|' COLLATE Latin1_General_CI_AI)) 
  END;
/* tempting. If only the correct way was as slick! */
GO
DECLARE @letters int
EXECUTE @letters=  #HowManylettersInWord 'predestination and science'; --execute our sample procedure
SELECT @letters
EXECUTE @letters=  #HowManylettersInWord 'level-crossing gates'; --execute our sample procedure
SELECT @letters

當被逼到角落時，任何灰色的數據庫開發(fā)人員都會承認使用RETURN代碼執(zhí)行此操作?，F在我們沒有必要對這個SQL Code Smell視而不見。當您從一個過程傳遞值時，您可以在豐富的數據類型中擁有任意數量的OUTPUT參數，并以一種即使是最無聊或最缺乏經驗的團隊成員都可以找到的方式命名它們。

但是，最好保持返回錯誤和問題的慣例，RETURN值是顯而易見的。將存在與錯誤值對應的正整數，如果過程無法恢復，失敗則為NULL，或者對于應用程序級進程問題為負值。

在典型的批處理中，幾個存儲過程按順序執(zhí)行，但控制流程根據每個過程中發(fā)生的情況而變化。可能會發(fā)生不好的事情，您需要做出相應的反應。舉個例子，我們假設一個插入表中的過程；如果進程失敗，它需要返回適當的值。例如，如果結果是重復條目，則該過程應相應地向調用批處理報表，解釋違反業(yè)務規(guī)則的情況。但是，它可能由于完全不同的原因而失敗，例如死鎖或磁盤空間已用完。這些問題中的每一個可能需要針對調用批處理或應用程序的不同解決方案，嘗試插入的過程只需要返回相應的錯誤。由程序決定相應的反應。

作為對RETURN傳回的錯誤作出反應的一個例子，有一個不幸的情況是你的進程被選為死鎖犧牲品：

'Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction' (Msg 1205).

當然，它實際上應該會說，“稍等一會兒，然后重新運行事務”。在處理偶爾容易出現死鎖的進程時，啟動事務、調用過程、在程序的RETURN中捕獲錯誤1205 ，回滾事務，等待一小段時間并重試。

RETURN代碼的另一個用途是返回應用程序“流程”問題的負數，例如“客戶當前已暫停”、“超出信用額度”、“帳戶上的文件備注”或“拒絕銀行轉帳”。雖然為SQL Server錯誤保留了正數，但您可以使用負數表示應用程序進程錯誤。

這是一個簡單的示例，用于查看數據庫中是否存在城市。它使用正數表示SQL Server錯誤，使用負數表示流程問題（這個例子中的城市不存在）。這些流程問題通常在應用程序中處理得最好，因此返回一個整數并讓應用程序處理反應（例如表單上的提示，使用適當的語言）要簡單得多。

USE adventureworks2016
GO
CREATE PROCEDURE #CheckContactCity  
   (@cityName VARCHAR(50))  
AS  
BEGIN 
   DECLARE @CityExists int
   BEGIN try
     SELECT @CityExists = CASE when EXISTS (SELECT * 
       FROM adventureworks2016.Person.Address  
         WHERE City = @cityName) THEN 1 ELSE 0 end
   END TRY
   BEGIN CATCH
     RETURN Error_Number(); --return the error as a positive integer
   END CATCH
   IF @CityExists= 0 RETURN -100 --you've chosen this to mean 'city doesn't exist 
END  
Go
--now test it out
DECLARE @Return INT;
EXECUTE @Return = #CheckContactCity 'Denver'; --execute our sample procedure
SELECT @Return
--returns zero 'city does exist'
EXECUTE @Return = #CheckContactCity 'fougasse'; --execute our sample procedure
SELECT @Return
--returns -100 'city doesn't exist

對于您漂亮、整潔的代碼來說，這一切看起來都有些混亂，但是在程序正文中您需要的唯一RETURN關鍵字是指示失敗的那些，除非你希望在某個時候中止程序，因為沒有更多的事情可做取得成功。如果一個程序到達END，它就贏了，所以自動返回零而不需要告訴它。

結論

存儲過程應通知調用它的進程是否成功。存儲過程返回一個整數值，應該由調用它的SQL批處理或應用程序捕獲和檢查。成功由零（0）表示。

但是，成功可能意味著許多事情。一個過程可以完全沒有錯誤，但它可能在業(yè)務流程方面失敗了。按照慣例，返回值中的正數是SQL Server錯誤的消息ID，您可以自由地為您遇到的任何應用程序進程問題分配負值。

與RETURN相關的四個SQL代碼氣味，換句話說，值得檢查或審查的編碼實踐：

1、發(fā)生錯誤時，不會傳回非零RETURN代碼以通知調用方存儲過程。

2、如果沒有整數參數，則使用RETURN關鍵字。 （BP0016）

3、發(fā)生錯誤時，無法對存儲過程返回的值做出適當的響應。

4、使用RETURN作為流程的一部分傳遞值，例如給定日期的購買數量，而不是流程的成功或失敗。

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊

掃描關注慧聚IT微信公眾號，及時獲取最新動態(tài)及最新資訊