Differences between Stored Procedures and Functions
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
9) UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
10) UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
11) Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
In details
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.
User defined functions have three main categories:
Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
Inline function - can contain a single SELECT statement.
Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.
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
9) UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
10) UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
11) Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
In details
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.
User defined functions have three main categories:
Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
Inline function - can contain a single SELECT statement.
Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.
No comments:
Post a Comment