SQL – Stored Procedures vs Functions

We can observe the advantages of stored procedures as well as of SQL functions before differentiate them with each other as shown below:

A Stored Procedure is a program (or procedure) which is physically stored within a database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

Stored procedures are stored in a pre-complied form and stores in our database. That is once a stored procedure is executed, the compiled code is used in subsequent calls. One more thing is if we can wrong execute store procedure the transaction may rollback.

A User-Defined Function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views. functions are compiled each and every time whenever you called them. 

Interesting facts, SQL Server functions, like cursors, are meant to be used as our last weapon! They do have performance issues and therefore using a table-valued function should be avoided as much as possible. 

We can easily differentiate them based on the following contents-

Contents	Stored Procedure	Functions
Return Type	They can return zero or n values.	function can return one value which is mandatory
Command Type	They can have select statements as well as DML statements such as insert, update, delete and so on	Function can allow only Select statements and can only read data, cannot modify the database. They will not allow using DML statements.
Parameters	Procedures can have input as well as output parameters as per the requirement.	Functions can have only input parameters.
Dynamic SQL	They can create and use Dynamic SQL.	They cannot use a Dynamic SQL inside a UDF.
Exception Handling	Exception can be handled by try-catch block in a procedure.	Try-catch block cannot be used in a function.
Transaction Management	Stored Procedures support the transaction management (BEGIN TRANSACTION, COMMIT, and ROLLBACK).	Function does not support the transaction management.
Join Clause	Procedures can't be used in Join clause.	A UDF can be used in join clause as a result set. They can use with SELECT statement, JOINS & APPLY (CROSS & OUTER)
XML FOR Clause	They can use used with XML FOR clause.	They cannot use used with XML FOR clause.
Built-in functions	They can execute all kinds of functions, be it deterministic or non-deterministic.	They cannot execute some non-deterministic built-in functions, like GETDATE().
Constraints	They cannot be used to create constraints while creating a table.	They can be used to create constraints while creating a table.
Others	They can work smoothly with table variable as well as temporary tables. They can't be called from Select/Where/Having and so on statements and Execute/Exec statement can be used to call/execute Stored Procedure. Stored Procedures can call functions.	Function allows table variables but not allows using temporary tables. Functions can be called from the select statement. A function cannot call a Stored Procedures.

Conclusion

Stored Procedures and Functions are used for some specific purpose. The nice thing is that you can easily put frequently used code into a function and return them as a column in a result set. We might use a function for a parameterized list of somethings. They are also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement.

We can say that stored procedures are like small programs in SQL Server. They can be simple as a select statement or as more complex based on our needs as a long script (DML statements) that adds, deletes, updates and/or reads data from multiple tables in a database.

Learn more on another feature of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
8. SQL - Merge Statement
9. SQL - Functions
10. SQL - Cursors