Ravendra's Blog

May 24, 2010

Stored procedure vs User defined functions

Filed under: sql — Ravendra Mishra @ 1:20 pm

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.

 

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.

Advertisements

Blog at WordPress.com.