Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Difference between Stored-Procedure and Function

Posted on 2004-04-19
Medium Priority
Last Modified: 2011-08-18
Hi folks,

I need differences between stored-procedure and functions with an example.
What are the differences between stored-procedure and triggers?
what is a view?

Question by:pampa34
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 10857247

What specific problem are you trying to solve using a sp,ufd or trigger ?

if this is a homework question it is not allowed here,

Author Comment

ID: 10857301
Its not a homework question? Generally what is the difference between a stored-proceudre and a function, how does the selection of these two enhance the performance. Which is the best?

Accepted Solution

SNilsson earned 100 total points
ID: 10857358

Well both are pre-compilled and run faster than a vanilla T-sql statement (assuming you are using sql server).

Which is 'best' depends on what operation you performing.

Have a look here to see the main differences:
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Assisted Solution

ora-dba earned 100 total points
ID: 10858223
the following definitions may help.
A procedure or function is an object stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values. Procedures and functions let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.

Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not.

Triggers are similar to stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by database when a triggering event occurs (when an INSERT, UPDATE, or DELETE statement is issued against the associated table or when database system actions occur), no matter which user is connected or which application is being used.

A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.

Expert Comment

ID: 10864523
It sounds like knowing which product you are working with is important as ora-dba's description of functions is very different than how functions are implemented in MS SQL Server.

Functions in MS SQL Server can be one of three different types (two letter abbrv from sysobjects):
      'FN', 'Function: Scalar Return'
      'IF', 'Function: Rowset Return'
      'TF', 'Function: Table Return'
The beatuty of functions is that the result of the function can be used within a SQL staement.  For instance, you could use the 'IF' type function in a FROM clause as the source data for a JOIN  condition.  The IF type function is a dynamically created rowset, so you don't need to know what the contents will be when defining the function.  The TF type function requires you to define the table structure you'll be returning, but still can be used in the FROM.  It would seem that the IF function would always be the more flexible way to go, however, a rowset return (IF type) function cannot have a begin...end section in it which is sometimes necessary for cursor operations or multiple SELECT statements.  The scalar return can be used in the SELECT's field list to return a value that will form a column in a row in the resultset.  Any of the functions can be passed parameters, however, a field in a query cannot be passed as a parameter.

In contrast, a stored procedure's result set or return value cannot be used from within a SQL statement.  A stored procedure allows all the functionality provided by a function, but the reverse is not true.  Sometimes, you must use a SP.  Sometimes you'll want to use a SP for performance reasons.  It used to be the case that MS SQL Server precompiled execution plans for stored procedures, however, it only caches recently used execution plans now.

Views are most similar to tables or rowset return functions (IF) because you must accomplish everything you need to query in a single SELECT statement.  Views can't be indexed with SQL Server unless you purchase the Enterprise edition of SQl Server.  One advantage to views is that you can enforce user security with views so that a user doesn't need to have rights to modify all of the columns in a table, but can modify whatever columns are provided by the view.

A trigger is a block of statements executed when an insert, update, or delete ocurrs on the table.  Since they are tied to the tables, any application that modifies the table will fire the trigger's procedure.  This is useful for enforcing data integrity or business rules at the database level (centralizing rules) when multiple applications may have access to the same DB and the DBA doesn't control or specify functionality in the applications.

Expert Comment

ID: 11045898
Well I guess it is hard to split 50 points 3 ways.

Expert Comment

ID: 11285956
1) In SQL Server, there is a RETURN clause that we can add in stored procedures. So if stored    procedures can return a value, then what's difference between stored procedure and function ?
2) If I have to make a choice between stored procedures and functions then what things shall I take into considerations for using either of these two ? Also how can we evaluate that either of these two is giving better performance factor ?

Expert Comment

ID: 11285999
SPs can only return an INT value.  You can return rowsets, tables, VARCHARs, INTs, and other data types with a FUNCTION.

As I understand things, the FUNCTION can rarely, if ever, out-perform a stored procedure, however, the FUNCTION has features like those discussed above that are unmatched by the SP.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question