SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What Your SP Can Return


What Your SP Can Return

Author
Message
Robert W Marda
Robert W Marda
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4437 Visits: 113
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
Robert W Marda
Robert W Marda
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4437 Visits: 113
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
hurcane
hurcane
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 254
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.



Philip Kelley
Philip Kelley
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2385 Visits: 232
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



Tatsu
Tatsu
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 307
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
Robert W Marda
Robert W Marda
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4437 Visits: 113
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
Ezz Khayyat
Ezz Khayyat
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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
Frances L
Frances L
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search