Solved

Difference between Stored-Procedure and Function

Posted on 2004-04-19
10
98,789 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
10 Comments
 
LVL 8

Expert Comment

by:SNilsson
Comment Utility

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
Comment Utility
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
Comment Utility

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
 
LVL 1

Assisted Solution

by:ora-dba
ora-dba earned 25 total points
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 9

Expert Comment

by:rherguth
Comment Utility
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
Comment Utility
Well I guess it is hard to split 50 points 3 ways.
0
 

Expert Comment

by:Harshal_Pandit
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now