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

點擊下載SQL Prompt免費版

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

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

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

什么是RETURN關(guān)鍵字，它返回什么？

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

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

我們可以證明，即使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

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

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

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;

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

SELECT * FROM dbo.MissingTable

…代替…

SELECT 1 / 0;

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

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

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

Code Smells和RETURN值

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

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

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

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

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

作為對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).

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

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

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

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關(guān)鍵字是指示失敗的那些，除非你希望在某個時候中止程序，因為沒有更多的事情可做取得成功。如果一個程序到達END，它就贏了，所以自動返回零而不需要告訴它。

結(jié)論

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

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

與RETURN相關(guān)的四個SQL代碼氣味，換句話說，值得檢查或?qū)彶榈木幋a實踐：

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

2、如果沒有整數(shù)參數(shù)，則使用RETURN關(guān)鍵字。 （BP0016）

3、發(fā)生錯誤時，無法對存儲過程返回的值做出適當?shù)捻憫?/p>

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

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點擊

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