Difference between Stored-Procedure and Function

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?

Who is Participating?
SNilssonConnect With a Mentor Commented:

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:

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,
pampa34Author Commented:
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?
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

ora-dbaConnect With a Mentor Commented:
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.
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.
Well I guess it is hard to split 50 points 3 ways.
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 ?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.