Adding to what Mike C# said..
You cannot modify the tables' data in a function but you can, in a stored
proc.
You cannot call the stored procedure in a query but you can call the
functions..
A stored procedure can call a function but a function cannot call a stored
procedure

And if you are a .net or java developer.. stored procedure is comparable to
the Main() function in your console application.. and functions are just the
other functions, assemblies and all that... functions cannot and should not
(my perspective completely) map to a business requirement.. you build a
function to aid the requirement demanded by a stored procedure..

I would write a scalar function to store a common business agnostic
functionality
and a Table valued function as parameterized views. Anything other than this
is a special case and is left to your discreetion.

HTH
--
-Omnibuzz

http://omnibuzz-sql.blogspot.com/

http://www.sql-server-performance.com/stored_procedures.asp
http://www.sql-server-performance.com/user_defined_functions.asp


[quoted text, click to view]

[quoted text, click to view]

Functions normally return a single scalar value or a single table/recordset.
A stored procedure can generate multiple tables/recordsets to be returned to
the caller, it can return scalar values via output parameters, and it can
return a single integer value separately (normally used as a status or error
indicator). User-defined functions have restrictions relating to
determinism and database side effects.

Which one you should use depends on what exactly you're trying to do...