Functions and Procedures in SQL Server

Question

What is the difference between a Function and a Procedure in SQL Server?

Answer 1

You can embed functions in SQL statements just like you would any native SQL funcion like COALESCE, CONVERT, etc. Procs can return a value, but the values that an be returned are limited. For example, if I recall correctly, you can return a table variable from a function, but not a stored proc.

So with a function, you can do things like this:

SELECT dbo.MyFunc(myColumn) as [Column Alias Name] FROM MyTable

or

SELECT * FROM dbo.MyTableVariableReturningFunc() as tbl

With stored procs, you can get the return value like so:

DELCARE @ReturnVal as int

EXEC @ReturnVal = USP_MyStoredProc

Answer 2

Another diffrence is that user defined functions can't modify database data, while procedures can.

Answer 3

Logically, to me, a function would be used to perform common tasks across the entire database in any query. A stored procedure on the other hand would be something I would use to perform complex tasks done on a regular basis but aren't common. For example, the stored procedures I typically write will grab data from a file and upload to a table (whilst performing many other operations in the process such as auditing etc.). It's not something that would be used anywhere else, but I would run it once a day.