Menu
I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that.
Can some one tell me why?
984
Functions are compiled and executed at run time. Stored procedures are stored in parsed and compiled state in the database. Only Select statements. DML statements like update & insert are not allowed.
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..
In SQL: A Procedure allows SELECT as well as DML ( INSERT , UPDATE , DELETE ) statements in it, whereas Function allows only SELECT statement in it. Procedures can not be utilized in a SELECT statement, whereas Functions can be embedded in a SELECT statement.
Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).
A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.
A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:
Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
