Difference between Stored Procedure and User Define Function in SQL Server 2005:

I have written many articles on differences and comparison on various topic of Microsoft SQL Server. Here is one more difference between Stored Procedure and User Define Function. Many times I have seen new developer in confusion about usage of functions so after finishing the series of articles in stored procedures and user define functions I thought to write one article for these differences.

Actually before Microsoft SQL Server 2000, there was no concept of UDFs and it has first been revealed in Microsoft SQL Server 2000 to wrap up your complex T-SQL logic but initially people were not started adopt it but I guess they have started focusing in it with Microsoft SQL Server 2005 when UDFs comes up with new features like table valued function and CLR function implementation. Now, the situation is, UDFs become one powerful tool in SQL Server kit.

The main difference I feel between SPs and UDFs is usage in SELECT. One can use UDFs anywhere in JOIN, FROM, WHERE and HAVING clause whereas SPs are not that much flexible.

UDFs are simple to invoke in any T-SQL statement then SPs.

Some DML statements like INSERT, UPDATE and DELETE are not permissible in UDFs whereas you can use it in SPs.

You can not call non-deterministic function of SQL Server inside the UDFs whereas you can call them in SPs. For example GETDATE() function, it is non-deterministic and can’t be called within UDFs.

Stored Procedure can call function but function can't call stored procedure.

User defined function can have only input parameter whereas SPs can have input as well as output parameter.

You can use Try...Catch in SPs whereas UDF can't support it.

If you are more interested to learn about SPs and UDFs than you can sear http://www.sqlhub.com for more details as I wrote dozens of article on this topic with so many real life examples.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com