>I'm compiling some training resources for a new hire, and I need to come up with a decent list of differences between Stored Procs and UDFs in SQL Server and why you should use which.
>
>Here's what I have off the top of my head:
>
>A UDF is more easily joined into another query.
>A UDF cannot update data, only retrieve.
>
>I thought I had more. Help me out, folks.
Like Mike said, scalar UDFs can really impact performance in a bad way.
Yes, a UDF cannot update, only retrieve.
On using a UDF to join to another query...."sort of". You can have a table-valued function and use it like a derived table. When you want to apply individual rows from a TVF to other tables, you actually have to use CROSS APPLY. Not saying you were incorrect, just qualifying the concept.
Table valued functions also serve another great role - since views cannot receive parameters, people use views along with TVFs as a security feature (for access to rows)
Stored procs are for insert/update/deletes, or for bringing back full result sets. You can't use a stored proc to directly join (or apply) with other tables - not without redirecting to a temp table first.
