Diffrence Between Stored Procedure And Function?

ashok maram

9y890

Diffrence Between Stored Procedure And Function in Sql Serever?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Puneet Kankar9y ago

Accepted answer

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (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        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

Naveen Bisht9y ago
one most important diffrance

Function is compiled it will genrate the error at complie time, while store proc will genrate the error at run time (time of exectuion).

for eg.

1- create a store proc with simple select query with table(which is not exists) it will create but it will give you error at run time

2- while in same case fution will not create
Zakir Ahamed9y ago
Use this URL:

http://www.c-sharpcorner.com/UploadFile/2072a9/store-procedure-vs-function-in-sql-server/

http://www.c-sharpcorner.com/UploadFile/996353/difference-between-stored-procedure-and-user-defined-functio/

https://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server