Stored Procedures vs Functions

Stored Procedures :

• Stored procedures takes input and output parameters.
• We can call functions in Stored Procedures.
• Possible to use DML statements inside Stored Procedures.
• Stored Procedures can return any type of Data type.
• We can not use Stored procedures in SELECT Statements.
• Stored Procedures can accept 21000 parameters.
• Stored Procedures supports deferred Name Resolution.

Functions:

• Functions takes Input parameter only.
• We can not call stored procedures in functions
• Not possible to use DML statements inside the Functions.
• Functions can not return some of the data types like Image data type can not return.
• We can use Functions in SELECT Statements.
• Functions can accept only 1023 Parameters.
• Functions does not support Deferred Name Resolution

What is means by Deferred Name Resolution in SQL Server?

When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the sys.sql_modules catalog view.

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the sys.sql_modules catalog view and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

In the resolution stage, Microsoft SQL Server also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.

If procedure execution successfully passes the resolution stage, the Microsoft SQL Server query optimizer analyzes the Transact-SOL statements in the stored procedure and creates an execution plan.