Function vs. Stored Procedure in SQL Server

Question

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?

Answer 1

• It is mandatory for Function to return a value while it is not for stored procedure.
• Select statements only accepted in UDF while DML statements not required.
• Stored procedure accepts any statements as well as DML statements.
• UDF only allows inputs and not outputs.
• Stored procedure allows for both inputs and outputs.
• Catch blocks cannot be used in UDF but can be used in stored procedure.
• No transactions allowed in functions in UDF but in stored procedure they are allowed.
• Only table variables can be used in UDF and not temporary tables.
• Stored procedure allows for both table variables and temporary tables.
• UDF does not allow stored procedures to be called from functions while stored procedures allow calling of functions.
• UDF is used in join clause while stored procedures cannot be used in join clause.
• Stored procedure will always allow for return to zero. UDF, on the contrary, has values that must come - back to a predetermined point.

Answer 2

To decide on when to use what the following points might help-

1. Stored procedures can't return a table variable where as function can do that.

2. You can use stored procedures to alter the server environment parameters where as using functions you can't.

cheers

Answer 3

• Functions can be used in a select statement where as procedures cannot.

• Stored procedure takes both input and output parameters but Functions takes only input parameters.

• Functions cannot return values of type text, ntext, image & timestamps where as procedures can.

• Functions can be used as user defined datatypes in create table but procedures cannot.

***Eg:-create table <tablename>(name varchar(10),salary getsal(name))

Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get’s executed and the return Type is returned as the result set.

Answer 4

The difference between SP and UDF is listed below:

+---------------------------------+----------------------------------------+
| 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.    |                                        |
+---------------------------------+----------------------------------------+

Answer 5

SQL Server functions, like cursors, are meant to be used as your last weapon! They do have performance issues and therefore using a table-valued function should be avoided as much as possible. Talking about performance is talking about a table with more than 1,000,000 records hosted on a server on a middle-class hardware; otherwise you don't need to worry about the performance hit caused by the functions.

1. Never use a function to return a result-set to an external code (like ADO.Net)
2. Use views/stored procs combination as much as possible. you can recover from future grow-performance issues using the suggestions DTA (Database Tuning Adviser) would give you (like indexed views and statistics) --sometimes!

for further reference see: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html