Stored Procedure VS Function

Overview

Sometimes we need to store or access some amount of data or perform some DML or DDL operations over our data stored in a particular SQL Server database. So for performing a particular set of operations, we need to access and modify database again and again according to the required functionality.

The requirement is not to perform these operations, but maintain the efficiency and integrity too in our database.

To increase the performance of our database, we need to concern on several aspects during database design. One of the most useful operations for performing these operations as well as maintaining efficiency are as follows:

• Stored Procedure
• Functions

S-Proc VS Functions

S-Proc

• SP can return zero, single or multiple values
• We can use transaction in SP
• SP have both input & output parameters
• SP can be used with XML in the clause
• SP is used in changing server configuration
• SP allows DML statements as well as Select
• Functions can be called from a SP
• SP uses EXEC or EXECUTION

Functions

• UDF have only input parameter
• It returns atleast common variable
• We can't use transaction in UDF
• Can't be used with XML clause
• No use in changing Server configuration
• SP can't be called inside a UDF
• UDF simply uses select operation
• It allows only select