What Your SP Can Return

  • 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

  • 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

  • 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.

  • 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

  • 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

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • 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

  • 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

  • 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