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 PANDEY 29 Jul 2008 | Member Level: Diamond Points : 0 |
Nice Article.........
|