Sql Server Programming :: Return code in a stored procedure


	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: - Hide quoted text - > 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? 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: - Hide quoted text - > > 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. 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 .... :-)