stored procedures that return a value

[iamsmahd]

Hi.
I have developed an application with SQL server 2000.Knowing that, I have almost 10 sp in it.
but still I feel I have trouble with stored procedures which return a value......I mean, as we
can develop a sp that returns a value, by specifying an "out put" parameter in parameter list of it
or without out put parameter we can develop it using "return" keyword...so when we should use first
methos and when the second?

Thank you

 

[NoCoolHandle]

Return parameters have a single limitation... they can only return a single Integer value...

I usually reserve them for retruning a success of failure status.. 0=failed 1=success condition1 2=success condition2 etc...

They can also be ok at returning an integer based primary key.. However because a return parameter is built in, it will be harder to know how to use it without looking at teh create text.

Output parameters.. very rich. They can return almost anything. They are self documenting via good naming and datatypes.. You can have more than one (1024?)..

Sooo

I tend to use output parameters to return data, and return parameters to return status info...


HTH


Rob