Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Return code in a stored procedure


Hi,

I see return code such as "RETURN 0" at the end of a stored procedure.
The stored procedure is really meant to return a dataset (by a
"SELECT" statement).

The definition of the stored procedure is below:

CREATE PROCEDURE [dbo].[spReportTypeGet]
        @ReportTypeID DTTypeID
AS

        SELECT
                rt.ReportTypeID,
                rt.PackageName
        FROM
                ReportType rt
        WHERE
                rt.ReportTypeID = @ReportTypeID

        RETURN 0

Anyone can tell me why use "RETURN 0" here?

On Jun 4, 2:48 pm, Curious <fir5tsi@yahoo.com> wrote:

In addition to any rowsets they return, stored procedures MAY also
return a return code, which is either an integer value or NULL. The
calling application may have various ways of retrieving it; in T-SQL
you can get it thus:

DECLARE @rtn INT
DECLARE @parm DTTypeID
-- I don't know what a DTTypeID is, so I'll assume you set it here
EXEC @rtn=[dbo].[spReportTypeGet], @parm

The variable @rtn would then have a zero in it.

Why this was done in this particular case is hard to say. A zero
return value may have been meant as an indication that no error
occurred, as is common in some other languges.

-----------------------------------------------Reply-----------------------------------------------

> In addition to any rowsets they return, stored procedures MAY also
> return a return code, which is either an integer value or NULL. The
> calling application may have various ways of retrieving it; in T-SQL
> you can get it thus:

> DECLARE @rtn INT
> DECLARE @parm DTTypeID
> -- I don't know what a DTTypeID is, so I'll assume you set it here
> EXEC @rtn=[dbo].[spReportTypeGet], @parm

> The variable @rtn would then have a zero in it.

Thanks for the explanation! It's clear. However, I still wonder if the
following is the best way to use the stored procedure:

DECLARE @rowset DATASET
DECLARE @parm DTTypeID
EXEC @rowset =[dbo].[spReportTypeGet], @parm

Because the stored procedure should return a dataset.

-----------------------------------------------Reply-----------------------------------------------

On Jun 4, 4:54 pm, Curious <fir5tsi@yahoo.com> wrote:

No, not at all. There are two conflicting meanings of "return" here.
Only an integer can be a return *value*. But the stored procedure can
also return -- better termed as "create", rather than return --  zero,
one, OR MORE *rowsets* (which may or may not be related to each
other).  The two different things come by completely different paths.

The only way to capture a rowset that a stored procedure creates
within T-SQL is by means of an INSERT statement:

INSERT someTable EXEC procedurename, param, param ...

And even in that case you can still capture the return value
separately:

INSERT someTable EXEC @rtn=procedurename, param, param ...

To increase the amount of confusion, someTable can be a table-valued
*variable* instead of a real table .... :-)

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc