Clever Geek Handbook

Functions and Procedures in SQL Server

What is the difference between a function and a procedure in SQL Server?

0


source to share


3 answers


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

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

When saving procs, you can get the return value like this:

DELCARE @ReturnVal as int

EXEC @ReturnVal = USP_MyStoredProc
+1


source


Another difference is that UDFs cannot modify database data, while procedures can.



+1


source


Logically for me, the function will be used to perform common tasks across the entire database in any query. A stored procedure, on the other hand, would be what I would use to perform complex tasks that are done on a regular basis but are not general. For example, the stored procedures that I usually write will grab data from a file and load it into a table (while doing many other operations in the process such as auditing, etc.). It is not something that will be used anywhere else, but I will be running it once a day.

0


source






More articles:


All Articles