Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Comparison in Procedures and Functions

Comparison in Procedures and Functions

Comparison in Procedures and Functions

I have just started using procedures and functions
In comparison I have seen that
--Proedures can return values but a function must
--Procedures used in a format like function are
  more convinient to use than functions
--Procedures can return multiple values but a function

If my observations are correct can anyone please tell me
why to use function itself?
Do they have any advantage over procedures?

Thanking you in advance

RE: Comparison in Procedures and Functions

Functions are very useful because they add custom functionality to your SQL.  Functions can be called from within a SQL statement; procedures cannot.
If you have a particular format you like to see dates displayed in, you could create a function called timestamp.  Then, instead of having to do a to_char(...) every time you display a date, you can do a simple

SELECT ..., timestamp(the_date_field)

RE: Comparison in Procedures and Functions

Thank you carp
I hope your information will be of great use to me

RE: Comparison in Procedures and Functions

Amol -
Perhaps this would be a more helpful example of the utility of a function.
Suppose you are building an application to support order transactions.   Let's assume you have several procedures that need to factor in sales tax, which is currently 2%.  One way to do this is to code the logic to calculate this tax in each procedure.  Another approach is to create a function called sales_tax that takes in the price of goods sold and returns the applicable sales tax.  Each procedure can then just call this function.

Now let us assume that Al Gore wins the election and you now have to increase the tax rate to 15%.  If you took the first approach, you have to make changes to all of the procedures that calculate sales tax.  If you miss one, you now have different business rules being applied, depending on which procedure is being executed.  On the other hand, if you use one function to calculate sales tax, you only have to modify this one function and all of the procedures will use the same, new tax rate.

RE: Comparison in Procedures and Functions

I just want to know where he lives that has a 2% sales tax...

Terry M. Hoey

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

RE: Comparison in Procedures and Functions

Mike -
No matter how subtle I try to be, I just can't slide ANYTHING past you!

RE: Comparison in Procedures and Functions

Thanks carp
seems you are of Mr Bush

you can inform this to our prime minister Mr. Vajpayee who's currently in US for discussions with both again


RE: Comparison in Procedures and Functions

Not particularly.  Once again, I'm faced with the lesser of two evils!  But we digress!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close