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