Difference between stored procedures and functions in Asp.net ?
Here we are giving main differences between the functions and stored procedures
·
Stored Procedure can return zero or n values whereas function can
return 1 value.Function must return a value which is mandatory but in stored
procedure it is optional. Procedure can return multiple values(max1024).
·
Stored Procedures can have input/output parameters for it whereas
functions can have only input parameters for it.
·
Stored Procedures can be used to read and modify data where as
functions can only read data, cannot modify the database.
·
Stored Procedure cannot JOIN a SP in a SELECT statement where as
in function Can JOIN a UDF in a SELECT statement.
·
Stored procedure is compiled for first time and compiled format is
saved and executes compiled code whenever it is called. But function is
compiled and executed every time it is called. This is the main advantage to
use stored procedures it will decrease burden on server.
·
Functions can be called from select statement, but stored
procedures cannot be called from select statement.
·
In Stored procedure can use Table Variables as well as Temporary
Tables inside an SP where as in function cannot use a Temporary Table, only
Table Variables can be used.
·
Procedure allows select as well as DML statement like update,
insert, and delete commands in it whereas function allows only select statement
in it.
·
Functions can be called from procedure using EXEC or EXECUTE
keyword whereas procedures cannot be called from function.
·
We can use try catch statements in stored procedures but in
functions we cannot use.
·
We can go for transaction management in stored procedure whereas
we can't go in function.
·
Procedures cannot be utilized in a select statement whereas
function can be embedded in a select statement.
·
Stored procedures Can use used with XML FOR clause where as
functions Cannot be used with XML FOR clause.
·
Stored procedures cannot be used to create constraints while
creating a table where as functions can be used to create Constraints while
creating a table.
·
Procedure cannot be used in SQL queries whereas function can be
used in SQL Queries.
·
A function can call directly by SQL statement like select
func_name from dual.
·
User Defined Functions can be used in the SQL statements anywhere
in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
·
User Defined Functions that return tables can be treated as
another rowset. This can be used in JOINs with other tables.
·
Inline User Defined Functions can be thought of as views that take
parameters and can be used in JOINs and other Rowset operations.
Latest Walkins 2013(updated every day )
0 comments:
Post a Comment