Output value, Return Parameter, and/or Raiserror? 2

[LNBruno]

Hi, all.

Our dev team had a bit of a heated discussion about when to use output values, return parameters and rasierror messages in stored procs for a new project. Naturally, I figured I'd just ask the pros!

So, at the risk of opening a huge can of worms, what do you all think? Is there method to the madness, or just personal preference?

TIA!

< M!ke >

 

[bborissov]

From SP I return two values:

0-success
<>0 - error number

If I need something additional I use OUTPUT parameters to get what I need (of course if i need it not in recordset type).

But this is not an axiom This is just my preference and code style.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mrdenny]

Each has it's use.

If you need to return data from a stored procedure to the calling code (such as a parent stored procedure) then output paramaters are the way to go.

Return values are used to deturmin if an error occured when running the stored procedure.

RAISERROR should be used if you need to pass the error message back to the calling application (such as a Windows App).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com