Return Values from Stored Procedures

All stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0.
Whether these negative numbers have any meaning, is a bit difficult to tell. It used to be the case, that the return values -1 to -99 were reserved for system-generated return values, and Books Online for earlier versions of SQL Server specified meanings for values -1 to -14. However, Books Online for SQL 2000 is silent on any such reservations, and does not explain what -1 to -14 would mean.
With some occasional exception, the system stored procedures that Microsoft ships with SQL Server return 0 to indicate success and any non-zero value indicates failure.
While there is no law that requires you to follow the same convention for your stored procedures, my strong recommendation is that you use return values solely to indicate success/failure. If you want to return data such as the id for an inserted row, number of affected rows or whatever, use an OUTPUT parameter instead. It follows from the fact that a blank RETURN may return 0, even if there has been an error during execution, that you should be careful to return an explict value yourself if an error occurs in the procedure.
There is one situation when a stored procedure does not return any value at all, leaving the variable receiving the return value unaffected. This is when the procedure is aborted because of a scope-aborting error. We will look more into this later. There is also one situation when the return value is NULL: this happens with remote procedures and occurs when the batch is aborted on the remote server. (Batch-abortion is also something we will look into more later on.)
There is one curious exception to the rule that @@error is set after each statement: a RETURN without parameters does not change the value of @@error, but leaves the variable unchanged. In my opinion, this is not really practically useful.

Source :- http://www.sommarskog.se/error-handling-I.html

Comments

Post a Comment

Popular posts from this blog

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

@@rowcount

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.
Read more

Sql Index

Image
Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Read more