Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What Your SP Can Return Expand / Collapse
Author
Message
Posted Sunday, July 13, 2003 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/whatyourspcanreturn.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #14120
Posted Tuesday, August 05, 2003 5:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #70378
Posted Tuesday, August 05, 2003 7:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 1:40 PM
Points: 144, Visits: 207
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.




Post #70379
Posted Tuesday, August 05, 2003 7:32 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 12:35 PM
Points: 649, Visits: 205
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





Post #70380
Posted Tuesday, August 05, 2003 4:05 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 291, Visits: 253
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.
bbyrd@intellithought.com


Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #70381
Posted Wednesday, August 06, 2003 5:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #70382
Posted Friday, April 01, 2005 7:02 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 28, 2006 4:40 AM
Points: 27, Visits: 1

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
Post #171487
Posted Tuesday, December 06, 2005 9:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 18, 2011 6:11 PM
Points: 355, Visits: 299

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.

Post #242281
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse