If you have just started learning SQL, a question may arise in your mind "What is the difference between Stored procedure and function in SQL Server?", so today in this article, I will quickly list out important differences between functions and stored procedures in SQL server.
Stored Procedure in SQL server
A stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method. Procedures resemble constructs in other programming languages because they can:
Accept input parameters and return multiple values in the form of output parameters to the calling program.
Contain programming statements that perform operations in the database. These include calling other procedures.
Return a status value to a calling program to indicate success or failure (and the reason for failure).
For this example, I will be using AdventureWorks2012 SQL server sample database, if you don't have you can download it from the url https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
Now, let's create a stored procedure, which has input paramters as LastName,FirstName and gives output as FirstName, LastName, Department based on user first and last name from HumanResources.vEmployeeDepartmentHistory table
USE AdventureWorks2012; GO CREATE PROCEDURE HumanResources.uspGetEmployeesTest2 @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL; GO
You can execute the above stored procedure, using the new query as below
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar'; GO
Benefits of Stored procedures:
- Reduces server/client network traffic as the commands in a procedure are executed as a single batch of code.
- Provides stronger security.
- Easier to maintain, as when client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.
- Provides better performance as a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.
Functions (User-defined functions) in SQL server
A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. In other words, Function may contain a set of statement as stored procedure but generally we create function if there is some calculations which we can do frequently.
We will again use the same database AdventureWorks2012, so let's create a function in it as below, it returns an inline table-valued function in the AdventureWorks2012 database. It returns three columns
Name and the aggregate of year-to-date totals by store as
YTD Total for each product sold to the store.
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name ); GO
Now, you can invoke the function, run this query:
SELECT * FROM Sales.ufn_SalesByStore (602);
Difference between Stored procedure and Functions
Here is the list of differences:
- Stored procedure may or may not return a value but if it returns a value it must be of int data type,but function return the values of any data type.
- A stored procedure can return multiple parameters but a function can return only one value.
- We can't use Stored procedures in SELECT/WHERE/HAVING but We can use user-defined functions in SELECT/WHERE/HAVING statement.
- Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function
- We can use the function within the Queries, but for stroed procedure's we won't able to use it in the queries.
- Transaction management is not possible in functions but it is possible in Stored procedures.
- We can implicitely execute the stored procedure's. But function's we can't.
- Print function can not be called within the function but it can be called within the stored procedure.
- Exception handling can be done in Stored procedure but not in function.