When to use what functions vs Sprocs

Question

Hi

t-sql

I tried to read the difference between UDF and sprocs, here

http://realfantasy.wordpress.com/2009/01/05/sql-server-user-defined-function-udf-vs-stored-procedure-sp/

Do u know more differences? When to use what?

Thanks

Answer 1

In a nutshell, functions are supposed to return values and not cause any side effects, and stored procedures are supposed to change things, cause side effects and optionally return values.

Answer 2

Note that that article is not entirely correct. You can use functions like GETDATE() in a UDF in SQL Server 2005 and above. It was SQL Server 2000 where you couldn't.

The thing I'd be aware of is depending on how you use UDFs, they can have a big, negative impact on performance. As you can call them inline in your query, if you do so in (e.g.) a WHERE clause to apply a condition then that could potentially lead to many many calls to that function once for each row. This can produce horrible performance so is something to bear in mind.