? difference between return value and output param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

? difference between return value and output param

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-04 : 18:57:32

what is the difference between return value and output value in sqlparameter

Pls explain to me in plain and simple english since i m not very good with SQL server either
Thanks a lot

JBelthoff
Posting Yak Master

173 Posts

Posted - 2006-06-04 : 19:33:09

The return value is for returning error values. It can only be an integer datatype. You can specifically set it yourself or have SQL Server place the error into it for you like:

RETURN @@Error

An output parameter can be of any SQL Server datatype, varchar, bit, etc.. and can return anything you want by doing this.

declare @Outputvariable varchar(20)
Select @Outputvariable = 'JBelthoff'

So a stroed procedure might look like this.


Create Procedure dbo.Test

  @Name varchar,
  @OutputVal varchar(25) Output

As

Declare @ErrVal int

Insert Into MyTable ( Name )
Values ( @Name )

Select @ErrVal = @@Error

If @ErrVal <> 0
  Return (@ErrVal)

Else

  Select @OutputVal = Name
  From MyTable
  Where id = 12

  Return (@@Error)

Make sense?

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-04 : 22:49:30

Thanks a lot dear Belthoff, perfect explanation.
1/ If I undestood correctly: Return value returns an explicit integer value by our doing an explicit Return @ReturnValue within the procedure; whereas Output returns the output Param (foir example @ouputparam) just by declaring it of type Output at the begining and without puting the instruction return @ouputparam at the end of the stored proc , is that correct;
2/One little question: sometimes u use the @ twice like in @@Error, when do we use the @ twice

Thanks

JBelthoff
Posting Yak Master

173 Posts

Posted - 2006-06-05 : 10:02:45

Almost, Return will stop your stored procedure at the moment it is called and return whatever integer you put into it. It is a way of breaking out of your procedure if you need to. Once Return is called the SP stops processing.

Using 2 @@'s such as in @@Error is an SQL Function that reutrns the last error that was generated. Using one @ like @ErrorVal is a variable that you decalare somewhere. Check BOL for @@Error and others.

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-05 : 16:07:45

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources