Solved

Difference between Stored-Procedure and Function

Posted on 2004-04-19
10
98,942 Views
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?

Thanks,
Pampa
0
Comment
Question by:pampa34
[X]
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
10 Comments
 
LVL 8

Expert Comment

by:SNilsson
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,
0
 

Author Comment

by:pampa34
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?
0
 
LVL 8

Accepted Solution

by:
SNilsson earned 25 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:
http://builder.com.com/i/tr/cms/contentPics/u00320020206rsh01_01.gif
 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Assisted Solution

by:ora-dba
ora-dba earned 25 total points
ID: 10858223
Hello,
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.
0
 
LVL 9

Expert Comment

by:rherguth
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.
0
 
LVL 9

Expert Comment

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

Expert Comment

by:Harshal_Pandit
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 ?
0
 
LVL 9

Expert Comment

by:rherguth
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

632 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