Differences between Stored Procedures and Functions | Stored Procedures vs Functions (Difference between SP & UDF)

                                Hi Friends, in this article I will explain about what is the differences between stored procedures and functions in SQL Server. In many interviews this question is compulsory.

                             I already explained in the previous article Difference between TRUNCATE, DELETE and DROP commands in SQL SERVER.

Difference between stored procedures and functions:

• Stored Procedure can return zero or n values whereas function can return 1 value.Function must return a value which is mandatory but in stored procedure it is optional. Procedure can   return multiple values (max 1024).
• Stored Procedures can have input/output parameters for it whereas functions can have only input parameters for it.
• Stored Procedures can be used to read and modify data where as functions can only read data, cannot modify the database.
• Stored Procedure cannot JOIN a SP in a SELECT statement where as in function Can JOIN a UDF in a SELECT statement.
• Stored procedure is compiled for first time and compiled format is saved and executes compiled code whenever it is called. But function is compiled and executed every time it is called. This is the main advantage to use stored procedures it will decrease burden on server.
• Functions can be called from select statement, but stored procedures cannot be called from select statement.
• In Stored procedure can use Table Variables as well as Temporary Tables inside an SP where as in function cannot use a Temporary Table, only Table Variables can be used.
• Procedure allows select as well as DML statement like update, insert, and delete commands in it whereas function allows only select statement in it.
• Functions can be called from procedure using EXEC or EXECUTE keyword whereas procedures cannot be called from function.
• We can use try catch statements in stored procedures but in functions we cannot use.
• We can go for transaction management in stored procedure whereas we can't go in function.
• Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
• Stored procedures Can use used with XML FOR clause where as functions Cannot be used with XML FOR clause.
• Stored procedures cannot be used to create constraints while creating a table where as functions can be used to create Constraints while creating a table.
• Procedure cannot be used in SQL queries whereas function can be used in SQL Queries.
• A function can call directly by SQL statement like select func_name from dual.
• User Defined Functions can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
• User Defined Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• Inline User Defined Functions can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

Go to previous interview question Difference between TRUNCATE, DELETE and DROP commands in SQL SERVER.