SQLServerCentral / Discuss Content Posted by Robert Marda / Article Discussions / Article Discussions by Author / What Your SP Can Return / Latest Posts

RE: What Your SP Can Return

Frances L — Tue, 06 Dec 2005 09:46:00 GMT

CREATE PROCEDURE get_DVCountasreturn (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.

RE: What Your SP Can Return

Ezz Khayyat — Fri, 01 Apr 2005 07:02:00 GMT

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 spXAS BEGIN TRAN EXEC spY EXEC spZ END TRANGO

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 spXAS 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 TRANGO

CREATE PROCEDURE spYAS -- do whatever SQL statements you want here... RETURN @@ERRORGO

CREATE PROCEDURE spZAS -- do whatever SQL statements you want here... RETURN @@ERRORGO

RE: What Your SP Can Return

Robert W Marda — Wed, 06 Aug 2003 05:59:00 GMT

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. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: What Your SP Can Return

Tatsu — Tue, 05 Aug 2003 16:05:00 GMT

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, MCDBASr. SQL Server DBA/Systems AnalystIntellithought, Inc.bbyrd@intellithought.com

RE: What Your SP Can Return

Philip Kelley — Tue, 05 Aug 2003 07:32:00 GMT

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

RE: What Your SP Can Return

hurcane — Tue, 05 Aug 2003 07:07:00 GMT

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.

RE: What Your SP Can Return

Robert W Marda — Tue, 05 Aug 2003 05:53:00 GMT

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. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

What Your SP Can Return

Robert W Marda — Sun, 13 Jul 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/whatyourspcanreturn.asp