Sr No. User Defined Functions Stored Procedures
1 UDF are simpler to invoke than Stored Procedures from inside another SQL statement. They are Complex to Invoke.
2 SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions. GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create, but could not be used inside the function itself. Non-deterministic values are not allowed to be called from inside Stored Procedure.
3 The User Defined Function must be prefaced with the owner name, DBO in this case. Not mandatory.
4 When call function then the parameter must transmit full.
Ex:
CREATE FUNCTION ftest(@val01 varchar(10),@val02 varchar(10) )
CREATE PROCEDURE pTest(
@val01 varchar(10) = ‘10',
@val02 varchar(10) = NULL
)
Call:
dbo.ftest(@val01,@val02) —-> OK
dbo.ftest(@val01) —-> NOT OK
When call store procedure then the parameter allow empty.
CREATE PROCEDURE pTest(
@val01 varchar(10) = ‘10',
@val02 varchar(10) = NULL
)
Call:
dbo.ftest(@val01,@val02) —-> OK
dbo.ftest(@val01) —-> NOT OK
pTest(@val01,@val02) —-> OK
pTest(@val01) —-> OK
5 A UDF is always used to return a value or a table object. You can also get values back from a stored procedure by the return code (integer only) or an output parameter.
6 Function return type could be scalar or table or table values(SQL Server). Stored procedure returns always integer value by default zero.
7 Stored Procedure is pre compiled execution plan Functions are not precompiled.
8 Function returns only one value at a time. Stored Procedure returns more than one value at a time.
9 We can call the functions in SQL statements (select max(sal) from emp). We cannot call Stored Procedures in SQL Statements.
10 Function do not return the images, text. Stored Procedure returns all.
11 Functions are used for computations. Procedures can be used for performing business logic.
12 Functions MUST return a value. Procedures need not be.
13 Function parameters are always IN, no OUT is possible Stored procedures can have input and output parameters, while user defined functions only can have input parameters. Output parameters must be returned as return values.
14 UDF can be used in the SQL Statements anywhere in the WHERE/HAVING/SELECT section Stored procedures cannot be.
15 If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same rowset in your application the better choice would be a stored procedure.
16 Easy to create a function. Skill Expertise is required to create a stored Procedure.
 Attachments
UDF vs SP (19189-2433-UDF vs SP.docx)User Defined Function vs Stored Procedure (19189-27120-UDF vs SP.docx)
|
| Author: Bunty 29 Jun 2008 | Member Level: Diamond Points : 2 |
Hi,
Excellent information on UDF and SP.
Clearly explain when to use User Defined function and Stored Procedure and What are it's advantages and disadvantages.
Very useful for everyone.
Keep posting such useful information.
Thanks for sharing your knowledge.
Thanks and Regards
S.S.Bajoria
|
| Author: UltimateRengan 30 Jun 2008 | Member Level: Diamond Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: Anil Kumar Pandey 29 Jul 2008 | Member Level: Diamond Points : 0 |
Nice Article.........
|
| Author: sangeetha 12 May 2009 | Member Level: Gold Points : 2 |
Stored Procedure
================
A stored procedure is a program (or procedure) which is
physically stored within a database. They are usually written in
a proprietary database language like PL/SQL for Oracle database
or PL/PgSQL for PostgreSQL. The advantage of a stored procedure
is that when it is run, in response to a user request, it is run
directly by the database engine, which usually runs on a separate
database server. As such, it has direct access to the data it
needs to manipulate and only needs to send its results back to
the user, doing away with the overhead of communicating large
amounts of data back and forth.
User-defined function
=====================
A user-defined function is a routine that encapsulates useful
logic for use in other queries. While views are limited to a
single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.
1>Procedure can return zero or n values whereas function can return one value which is mandatory.
2>Procedures can have input,output parameters for it whereas functions can have only input parameters.
3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
4>Functions can be called from procedure whereas procedures cannot be called from function.
5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6>We can go for transaction management in procedure whereas we can't go in function.
7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
|