July 13, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/whatyourspcanreturn.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
August 5, 2003 at 5:53 am
By reading a recent post I learned that there is a time when the RETURN command does not halt the execution of a stored procedure. That is when you use a query with the RETURN command. If you do then the stored procedure will continue to execute. I learned about this exception while reading a discussion in the T-SQL forum. Thanks go to Mark Maddison for posting this exception.
My article was already submitted when I read this post. I hope to incorporate a portion of this post into the introduction of the article soon.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
August 5, 2003 at 7:07 am
I'd like to confirm that using the return value to return error messages is an excellent use of the return value. It makes stored procedures much like functions. In our architecture, we keep a table of business-specific errors. Consumers of our stored procedures can query the buiness error table to get a user-friendly description of the return code from the stored procedure.
Return codes can also be useful in communicating the number of rows that were affected by a complex procedure. For example, a procedure that is changing a flag based on a complex set of criteria can return how many records were changed. We use the return code both ways. Errors are always negative numbers and "affected records" is always a positive number.
August 5, 2003 at 7:32 am
I've got a question on this subject.
The base concept is that a (sub) routine will always return a value, such that
A = f(x)
This is expanded by differentiating between "procedures" and "functions"; where (in very general terms) procedures "do stuff" and functions return values.
Somewhere along the line (back when I was a mere programmer), I got the sense that the recommended (Microsoft?) best practice was to always have (1) procedures return "execution status" values, that is a code returning whether it worked or not--zero (success) or non-zero (value indicating nature of failure)--and (2) functions return the desired value [and I guess you had to hope they didn't error out].
Based on this, I've always tried to have our developers use the RETURN value to indicate either that the procedure worked as expected or that an error was encountered, with a code to indicate the nature of the error. All "procedural" return values get handled with output parameters or data sets.
The thing is, I've never read this anywhere, only kinda subliminally understood this to be true. I'd be interested to know if this synchs with anyone else's perception of how code should be designed.
Philip
August 5, 2003 at 4:05 pm
quote:
I've got a question on this subject.Somewhere along the line (back when I was a mere programmer), I got the sense that the recommended (Microsoft?) best practice was to always have (1) procedures return "execution status" values, that is a code returning whether it worked or not--zero (success) or non-zero (value indicating nature of failure)--and (2) functions return the desired value [and I guess you had to hope they didn't error out].
Based on this, I've always tried to have our developers use the RETURN value to indicate either that the procedure worked as expected or that an error was encountered, with a code to indicate the nature of the error. All "procedural" return values get handled with output parameters or data sets.
The thing is, I've never read this anywhere, only kinda subliminally understood this to be true. I'd be interested to know if this synchs with anyone else's perception of how code should be designed.
That is the understanding I have had as far back as I can remember and, from personal experience, I have to hold to it. In an environment with many developers, it is important to have consistency. Since a stored proc can only return an integer it is not very useful for returning application data most of the time. So, if you return an error code sometimes and application data other times it can get confusing for the developers that are using your procs.
Writing SQL stored procedures is a little different than writing procedures in a mainstream program language such as VB (.NET or otherwise) or C(insert optional/favorite symbol here) since the mainstream languages allow you to control the return type of a procedure.
That all leads to the good, old BOL which states under stored procedures, returning data (select Returning Data Using a Return Code from the Topics Found list) "A stored procedure can return an integer value called a return code to indicate the execution status of a procedure". Looks like Microsoft is pointing us toward using it for an error code.
You don't really need to return an error code since the error will bubble up wo whichever API you are using; but, you should be consistent about what you do return so you don't confuse others.
Bryant E. Byrd, MCDBA
Sr. SQL Server DBA/Systems Analyst
Intellithought, Inc.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
August 6, 2003 at 5:59 am
I have always used the RETURN code to return a number that indicates a status, it doesn't always indicate an error per say but a status of execution.
One example is that we have a stored procedure that allows you to search on an institution name. If there are more than one name that match the like search done on the name you enter then the SP delivers a list of all matching names with their ID's. If only one name matches then you get your search result. We return a code to tell our web site if it is getting the results or a list of institutions. Depending on the return code the web site will display a drop down with all the institutions that matched your search or your results.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
April 1, 2005 at 7:02 am
In my opinion, I always suggest to use return values as a Success/Failure indication. In the project I am working on, I have some nested stored procedures. spX calls spY and spZ within a transaction.
CREATE PROCEDURE spX
AS
BEGIN TRAN
EXEC spY
EXEC spZ
END TRAN
GO
In this case, if an error occured in spY, you can't tell about it, and the execution will continue to call spZ without rolling back the transaction, which is bad logic!
So I guess the best way to make use of return values is as follows:
CREATE PROCEDURE spX
AS
BEGIN TRAN
Declare @retval INT
EXEC @retval = spY
IF @retval IS NOT 0
ROLLBACK TRAN
EXEC @retval = spZ
IF @retval IS NOT 0
ROLLBACK TRAN
END TRAN
GO
CREATE PROCEDURE spY
AS
-- do whatever SQL statements you want here...
RETURN @@ERROR
GO
CREATE PROCEDURE spZ
AS
-- do whatever SQL statements you want here...
RETURN @@ERROR
GO
Ezz Khayyat
December 6, 2005 at 9:46 am
CREATE PROCEDURE get_DVCount
as
return (SELECT COUNT(*) AS CountOfDV from [Module Log] WHERE [Access Module Time] >= DATEADD(m,-1,DATEADD(m,DATEDIFF(m,0,GETDATE()),0)) AND [Access Module Time] < DATEADD(m,DATEDIFF(m,0,GETDATE()),0) GROUP BY [Module Name]
HAVING ([Module Name] = 'Domestic Violence'))
GO
DECLARE @DVCount int
EXEC @DVCount=get_DVCount
I used this before. It work right now did not return anything. I tried to run the query , it have row. can't figure out why.
Thx.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply