Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).
A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.
Functions and stored procedures serve separate purposes. Although it’s not the best analogy, functions can be viewed literally as any other function you’d use in any programming language, but stored procs are more like individual programs or a batch script.
Functions normally have an output and optionally inputs. The output can then be used as the input to another function (a SQL Server built-in such as DATEDIFF, LEN, etc) or as a predicate to a SQL Query – e.g., SELECT a, b, dbo.MyFunction(c) FROM table or SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).
Stored procs are used to bind SQL queries together in a transaction, and interface with the outside world. Frameworks such as ADO.NET, etc. can’t call a function directly, but they can call a stored proc directly.
User defined functions is an important tool available as a sql server programmer. You can use it inline in a sql statement like so select a, lookupValue(b), c from customers where lookupValue will be a Function. This kind of functionality is not possible when using stored procedure. At the same time you cannot do certain things inside a Function . The basic thing to remember here is functions cannot do things that can create permanent change – cannot change data where as a stored procedure can do those.
Never use a function to return a result-set to an external code (like dot.Net)
Use views/stored procs combination as much as possible.