SQL語法提示工具SQL Prompt——忽略使用或?yàn)E用RE

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

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

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

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

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

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

某些系統(tǒng)存儲(chǔ)過程會(huì)返回運(yùn)行批處理時(shí)發(fā)生的實(shí)際錯(cuò)誤代碼，包括程序中RAISERROR語句中指定的那些代碼，但是沒有標(biāo)準(zhǔn)表明您需要執(zhí)行此操作。實(shí)際上，文檔建議您可以根據(jù)執(zhí)行過程的錯(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ù)人日常編寫的簡單存儲(chǔ)過程不需要太多使用RETURN代碼，但是當(dāng)我們開始執(zhí)行更復(fù)雜的基于事務(wù)的處理時(shí)，這樣做的價(jià)值很快就會(huì)出現(xiàn)。

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

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

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

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

Code Smells和RETURN值

RETURN值只應(yīng)用于表示所執(zhí)行操作的成功或失敗，以及其原因。但是，在OUTPUT參數(shù)之前有一段時(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

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

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

在典型的批處理中，幾個(gè)存儲(chǔ)過程按順序執(zhí)行，但控制流程根據(jù)每個(gè)過程中發(fā)生的情況而變化。可能會(huì)發(fā)生不好的事情，您需要做出相應(yīng)的反應(yīng)。舉個(gè)例子，我們假設(shè)一個(gè)插入表中的過程；如果進(jìn)程失敗，它需要返回適當(dāng)?shù)闹?。例如，如果結(jié)果是重復(fù)條目，則該過程應(yīng)相應(yīng)地向調(diào)用批處理報(bào)表，解釋違反業(yè)務(wù)規(guī)則的情況。但是，它可能由于完全不同的原因而失敗，例如死鎖或磁盤空間已用完。這些問題中的每一個(gè)可能需要針對(duì)調(diào)用批處理或應(yīng)用程序的不同解決方案，嘗試插入的過程只需要返回相應(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ì)說，“稍等一會(huì)兒，然后重新運(yùn)行事務(wù)”。在處理偶爾容易出現(xiàn)死鎖的進(jìn)程時(shí)，啟動(dòng)事務(wù)、調(diào)用過程、在程序的RETURN中捕獲錯(cuò)誤1205 ，回滾事務(wù)，等待一小段時(shí)間并重試。

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

這是一個(gè)簡單的示例，用于查看數(shù)據(jù)庫中是否存在城市。它使用正數(shù)表示SQL Server錯(cuò)誤，使用負(fù)數(shù)表示流程問題（這個(gè)例子中的城市不存在）。這些流程問題通常在應(yīng)用程序中處理得最好，因此返回一個(gè)整數(shù)并讓應(yīng)用程序處理反應(yīng)（例如表單上的提示，使用適當(dāng)?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();?<a?id="post-505042-_Hlk15977359"></a>--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ì)于您漂亮、整潔的代碼來說，這一切看起來都有些混亂，但是在程序正文中您需要的唯一RETURN關(guān)鍵字是指示失敗的那些，除非你希望在某個(gè)時(shí)候中止程序，因?yàn)闆]有更多的事情可做取得成功。如果一個(gè)程序到達(dá)END，它就贏了，所以自動(dòng)返回零而不需要告訴它。

結(jié)論

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

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

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

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

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

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

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