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

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.