SQL語(yǔ)法提示工具SQL Prompt教程：忽略使用或?yàn)E用RETURN關(guān)鍵字（BP016）

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

點(diǎn)擊下載SQL Prompt免費(fèi)版

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

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

僅使用RETURN值來(lái)傳達(dá)流程的成功或失敗，永遠(yuǎn)不要將值作為流程的一部分返回，例如某一天的購(gòu)買數(shù)量。此外，存儲(chǔ)過(guò)程或批處理應(yīng)該永遠(yuǎn)不會(huì)有沒(méi)有值的RETURN關(guān)鍵字，如果SQL Prompt 檢測(cè)到此錯(cuò)誤，它將發(fā)出BP016警告。

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

要從報(bào)表任何問(wèn)題的任何存儲(chǔ)過(guò)程或批處理返回值，您需要將其分配給變量并使用RETURNcontrol-of-flow關(guān)鍵字，并將值作為參數(shù)。此RETURN將立即終止批處理的執(zhí)行，并返回您作為參數(shù)傳遞的值。如果嘗試使用RETURN關(guān)鍵字從存儲(chǔ)過(guò)程返回NULL，則會(huì)收到警告，并返回0。如果某個(gè)過(guò)程遇到需要立即終止的錯(cuò)誤，它將返回NULL，因?yàn)樗肋h(yuǎn)不會(huì)到達(dá)RETURN關(guān)鍵字或批處理的末尾！如果批處理或過(guò)程到達(dá)結(jié)尾，它將自動(dòng)返回零。

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

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

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ù)人日常編寫(xiě)的簡(jiǎn)單存儲(chǔ)過(guò)程不需要太多使用RETURN代碼，但是當(dāng)我們開(kāi)始執(zhí)行更復(fù)雜的基于事務(wù)的處理時(shí)，這樣做的價(jià)值很快就會(huì)出現(xiàn)。

讓我們從返回代表錯(cuò)誤號(hào)的代碼開(kāi)始。

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;

您會(huì)注意到在程序結(jié)束時(shí)無(wú)需添加RETURN 0，因?yàn)檫@是自動(dòng)完成的。如果到達(dá)批處理的末尾，SQL Server會(huì)認(rèn)為您已贏了，因此返回0。如果您嘗試執(zhí)行...

SELECT * FROM dbo.MissingTable

…代替…

SELECT 1 / 0;

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

• TRY…CATCH 不會(huì)捕獲嚴(yán)重性為10或更低的警告或信息性消息。

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

Code Smells和RETURN值

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

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

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

但是，最好保持返回錯(cuò)誤和問(wèn)題的慣例，RETURN值是顯而易見(jiàn)的。將存在與錯(cuò)誤值對(duì)應(yīng)的正整數(shù)，如果過(guò)程無(wú)法恢復(fù)，失敗則為NULL，或者對(duì)于應(yīng)用程序級(jí)進(jìn)程問(wèn)題為負(fù)值。

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

作為對(duì)RETURN傳回的錯(cuò)誤作出反應(yīng)的一個(gè)例子，有一個(gè)不幸的情況是你的進(jìn)程被選為死鎖犧牲品：

'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).

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

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

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

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

對(duì)于您漂亮、整潔的代碼來(lái)說(shuō)，這一切看起來(lái)都有些混亂，但是在程序正文中您需要的唯一RETURN關(guān)鍵字是指示失敗的那些，除非你希望在某個(gè)時(shí)候中止程序，因?yàn)闆](méi)有更多的事情可做取得成功。如果一個(gè)程序到達(dá)END，它就贏了，所以自動(dòng)返回零而不需要告訴它。

結(jié)論

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

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

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

1、發(fā)生錯(cuò)誤時(shí)，不會(huì)傳回非零RETURN代碼以通知調(diào)用方存儲(chǔ)過(guò)程。

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

3、發(fā)生錯(cuò)誤時(shí)，無(wú)法對(duì)存儲(chǔ)過(guò)程返回的值做出適當(dāng)?shù)捻憫?yīng)。

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

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

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