Fundamental difference between Stored procedure vs User Functions:
- Procedure may return none or more values.Function must always return one value either a scalar value or a table.
- Procedure have input,output parameters.Functions have only input parameters.
- Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
- Functions can be called from procedure.Procedures cannot be called from function.
- Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
- Transaction management possible in procedure but not in function.
Below are few differences between Stored Procedure and Function
1) Stored procedure are compiled for first time and compiled format is saved and executes compiled code when ever it is called. But function is compiled and executed every time it is called.
2) Function must return a value but in stored procedure it is optional.
3) Function takes one input parameter it is mandatory but stored procedure may take o to n input parameters.
4) Functions can be called from select statement, but stored procedures can not be called from select statement.
5) We can build logic in functions and we can even break complex logic in to methods.
6) We can use try catch statements in stored procedures but in functions we can not use.
7) We can not use insert,delete,update and create statements in functions but in stored procedures we can use those statements.
8 ) Functions can have only input parameters but stored procedure can have input and out put parameters
1) Stored procedure are compiled for first time and compiled format is saved and executes compiled code when ever it is called. But function is compiled and executed every time it is called.
2) Function must return a value but in stored procedure it is optional.
3) Function takes one input parameter it is mandatory but stored procedure may take o to n input parameters.
4) Functions can be called from select statement, but stored procedures can not be called from select statement.
5) We can build logic in functions and we can even break complex logic in to methods.
6) We can use try catch statements in stored procedures but in functions we can not use.
7) We can not use insert,delete,update and create statements in functions but in stored procedures we can use those statements.
8 ) Functions can have only input parameters but stored procedure can have input and out put parameters
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.
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.