Shulou(Shulou.com)06/01 Report--

SQL Prompt is a practical SQL grammar prompt tool. SQL Prompt automatically retrieves according to the object name, syntax and code snippet of the database to provide users with appropriate code choices. Automatic script settings make the code easy to read-especially useful when developers are not familiar with scripts. SQL Prompt can be installed and used, which can greatly improve the coding efficiency. In addition, users can customize it as needed to make it work in the desired way.

The article explains the correct use of the RETURN keyword in a stored procedure or batch, passes the non-zero return code to the calling process, notifies it of the error, and explains some misuse.

All stored procedures, statement blocks, and batches return a code that records the success of their execution. If the batch or procedure reaches the end, it automatically returns 0 (zero), which means success, using integer arguments unless you specify it using the RETURN keyword. Nothing is defined for any number other than 0, but the convention for the presence of any other number indicates some description failure. If an error occurs, you should capture the returned value and send it to the calling process so that it can respond accordingly.

Use only return values to communicate the success or failure of the process, and never return the value as part of the process, such as the number of purchases on a given day. In addition, stored procedures or batches should never have a RETURN keyword without a value, and if SQL Prompt detects this error, it will issue a BP016 warning.

What is the RETURN keyword and what does it return?

To return a value from any stored procedure or batch of any problem in the report, you need to assign it to a variable and use the RETURNcontrol-of-flow keyword with the value as a parameter. This RETURN immediately terminates the execution of the batch and returns the value you passed as a parameter. If you try to return NULL from a stored procedure using the RETURN keyword, you will receive a warning and return 0. If a procedure encounters an error that needs to be terminated immediately, it returns NULL because it never reaches the end of the RETURN keyword or batch! If the batch or procedure reaches the end, it automatically returns zero.

In fact, the documentation suggests that you can pass any value back to the callback batch based on errors in the execution process.

We can prove that even if sp_ExecuteSQL returns an error code generated by an error, it returns 0 if it is just a warning. If it is just a warning, it continues to execute, of course, if it succeeds, its return code is 0, and if it is not successful, it returns a failed error code.

Raiserror ('HELP, Illumination m trapped in this batchstones page5, 1) / * Msg 50000, Level 5, State 1, Line 25HELP, I'm trapped in this batchhammer raceme DECLARE @ Return intEXECUTE @ Return= sp_executeSQL N'raiserror (' 'HELP, Illumination records' m trapped in this batchstones') 'SELECT @ Return--returns 50000 (user-defined error). But what if we do a warning instead of an error?DECLARE @ Return intEXECUTE @ Return= sp_executeSQL N'raiserror (''HELP, Illustrated animals, m trapped in this batchstones)' SELECT @ Return--returns 0 because it was only a warningSELECT * FROM dbo.MissingTable/*Msg 208, Level 16, State 1, Line 40Invalid object name 'dbo.MissingTable'.*/DECLARE @ Return intEXECUTE @ Return= sp_executeSQL N'SELECT * FROM dbo.MissingTable'SELECT @ Return--returns 208

While the simple stored procedures that most of us write on a daily basis don't require much RETURN code, the value of doing so quickly becomes apparent when we start performing more complex transaction-based processing.

Let's start by returning the code that represents the error number.

CREATE PROCEDURE # TryoutProcedureAS 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;GODECLARE @ Return INT;EXECUTE @ Return = # TryoutProcedure;-- execute our sample procedureSELECT @ ReturnIF Coalesce (@ Return,0) 0 SELECT * FROM sys.messages-- and see if the error was passed back WHERE message_id = @ Return AND language_id = 1033

You will notice that there is no need to add RETURN 0 at the end of the program, because this is done automatically. If you reach the end of the batch, SQL Server thinks you have won, so it returns 0. If you try to execute.

SELECT * FROM dbo.MissingTable

... In lieu of

SELECT 1 / 0

... You will find an error returning NULL and "invalid object name". Why? It abandons the program instead of complying with TRY... CATCH construction. SQL Server cannot recover the batch from this error and abort NULL execution completely using a. As Sherlock Holmes said, these are deep waters. To be more specific:

TRY... CATCH does not capture warnings or informational messages with a severity of 10 or less.

TRY... CATCH can only be run while it is running. This means, for example, that errors with a severity of 20 or more cannot be caught, which prevent the SQL Server database engine task of the session from processing. This also applies to considerations, such as client interrupt requests or client connection breaks, and when the system administrator uses the KILL statement to end the session. If there is a compilation error (such as a syntax error), which will prevent the batch from running, it will never reach TRY. CATCH statement. This can also happen if an error occurs when parsing object names during any recompilation.

Code Smells and return valu

The RETURN value is applied only to indicate the success or failure of the operation performed, and why. However, for some time before the OUTPUT parameter, this return value is the only simple way to pass any type of integer value back to the batch.

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

When pushed into a corner, any gray database developer will admit to using RETURN code to do this. Now there is no need to turn a blind eye to this SQL Code Smell. When you pass values from a procedure, you can have any number of OUTPUT parameters in a rich data type and name them in a way that even the most boring or inexperienced team members can find.

However, it is best to maintain the convention of returning errors and problems, and the return value is obvious.

In a typical batch, several stored procedures are executed sequentially, but the control process changes according to what happens in each process. Something bad may happen and you need to react accordingly. For example, let's assume a process that inserts into a table; if the process fails, it needs to return the appropriate value. For example, if the result is a duplicate entry, the process should explain the violation of the business rule to the calling batch report accordingly. However, it may fail for completely different reasons, such as a deadlock or running out of disk space. Each of these problems may require a different solution for calling the batch or application, and the process of trying to insert only needs to return the corresponding error. The corresponding reaction is determined by the program.

As an example of responding to errors returned by RETURN, there is an unfortunate situation in which your process is chosen as a deadlock victim:

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

Of course, it should actually say, "wait a minute, and then rerun the transaction." When dealing with processes that are occasionally prone to deadlocks, start the transaction, call the procedure, catch error 1205 in the program's RETURN, roll back the transaction, wait a short period of time, and try again.

Another use of the RETURN code is to return a negative number of application "process" problems, such as "customer currently paused", "credit limit exceeded", "file comments on account", or "bank transfer denied". Although positive numbers are reserved for SQL Server errors, you can use negative numbers to indicate application process errors.

This is a simple example to see if a city exists in the database. It uses positive numbers for SQL Server errors and negative numbers for process problems (the city in this example does not exist). These process issues are usually handled best in the application, so it is much easier to return an integer and let the application handle the response (such as a prompt on a form, using the appropriate language).

USE adventureworks2016GOCREATE 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 outDECLARE @ Return INT EXECUTE @ Return = # CheckContactCity 'Denver';-- execute our sample procedureSELECT @ Return--returns zero' city does exist'EXECUTE @ Return = # CheckContactCity 'fougasse';-- execute our sample procedureSELECT @ Return--returns-100' city doesn't exist

All of this may seem messy for your beautiful, neat code, but the only RETURN keywords you need in the body of the program are those that indicate failure, unless you want to stop the program at some point, because there is nothing more to do to succeed. If a program reaches END, it wins, so it automatically returns zero without telling it.

Conclusion

The stored procedure should notify the process calling it whether it was successful or not. The stored procedure returns an integer value that should be captured and checked by the SQL batch or application that called it. Success is represented by zero (0).

But success can mean a lot of things. A process can be error-free at all, but it may fail in terms of business processes.

Four SQL code smells associated with RETURN, in other words, coding practices worth checking or reviewing:

1. When an error occurs, a non-zero return code is not returned to notify the caller of the stored procedure.

2. If there are no integer arguments, the RETURN keyword is used. (BP0016)

3. When an error occurs, you cannot respond appropriately to the value returned by the stored procedure.

4. Use RETURN to pass values as part of the process, such as the number of purchases on a given date, rather than the success or failure of the process.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.