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?
2Best Answer
The difference between SP and UDF is listed below:
| Stored Procedure (SP) | Function (UDF – User Defined) |
|---|---|
| SP can return zero, single or multiple values. | Function must return a single value (which may be a scalar or a table). |
| We can use transaction in SP. | We can’t use transaction in UDF. |
| SP can have input/output parameter. | Only input parameter. |
| We can call function from SP. | We can’t call SP from function. |
| We can’t use SP in SELECT/ WHERE/ HAVING statement. | We can use UDF in SELECT/ WHERE/ HAVING statement. |
| We can use exception handling using Try-Catch block in SP. | We can’t use Try-Catch block in UDF. |