In Focus

Stored Procedure Vs Function in SQL Server

This article explains what stored procedures and functions are and how they work and some basic differences among them.

This article will take you closer to Stored Procedures and functions. Some of you may be already familiar with these two most commonly used terms in SQL Server. This article will explain what Stored Procedures and functions are and how they work and some basic differences among them.
 

Overview

 
Sometimes we need to store or access some amount of data or do some DML or DDL operations over our data stored in a specific SQL Server database. So for performing specific set of operations, we need to access and modify the database, again and again, depending on the required functionality.
 
The requirement is to not perform these operations but maintains efficiency and integrity too in our database.
 
To increase performance of our database, we need to concern with several aspects during database design. One of the most useful operations for performing those operations as well as maintaining efficiency is as follows:
  • Stored Procedure
  • Functions

Stored Procedure in SQL Server

 
If we want to do certain repetitive tasks/operations over our database within the same application and database, then, in this case, the most useful method for this functioning is none other than Stored Procedures.
 
It is most often called an "S-Proc" or "SP".
 
Stored Procedure in SQL Server 
 

Pros of Stored Procedure

 
A SQL Server Stored Procedure provides us many advantages like:

      - SP can return zero, single or multiple values
      - We can use transaction in SP
      - SP has both input and output parameters
      - Can be used with XML for clause
      - Used in changing server configuration
      - SP allows DML statements as well as Selects

Hierarchy of Stored Procedure

 
The complete hierarchy of a Stored Procedure is shown below in a reference chart, from the root to the base level:
 
Hierarchy of Stored Procedure  
 

Types of Stored Procedure

 
A Stored Procedure is categorized in these two major categories:
 
Types of Stored Procedure 
 
System Stored Procedure
 
In SQL Server sometimes we need to do many informational or admin level tasks or activities. These sets of tasks can be done very easily using a System Stored Procedure. In this type of Stored Procedure whenever we do any modification or alteration in our database table, we do add a backup or some administrative level functioning in that modified database location.
 
All this functioning is done under the procedure of a system S-Proc. System Stored Procedures are prefixed by sp_, so it is not available to use sp_ for any other type of the Stored Procedure that we create until or unless they are part of our SQL Server installation.
 
User Stored Procedure
 
It is a program that is in general stored and compiled in our SQL Server and prefixed with sp_ like system Stored Procedure.
 
This type of Stored Procedure can be further categorized into three parts:
  • User Stored Procedure
  • User Defined Functions
  • Triggers

Creating Stored Procedure

 
Here's a SProc example:
 
Stored Procedure example 
 
Credits: #MSDN
 

Functions in SQL Server

 
We can define a function as:
 
"Functions are nothing but a Database object that is being created for implementing or handling certain types of complex functionalities. "
 
In general, functions are sets of SQL statements that only accept input parameters (compared with Store Procedures), depending on which it simply takes some sort of inputs and displays a result accordingly, nothing more or less than that.
 

Pros of SQL Server Functions

 
Some advantages of functions are:
  • Can be used with Clauses easily (like where, having and so on)
  • Function can be embedded in a Select statement
  • Easy to Use
  • Functionality Oriented

Hierarchy of SQL Server Function

 
The complete hierarchy of a function is shown below in a reference chart, from the root to the base level:
 
Hierarchy of SQL Server Function 
 

Types of SQL Server Function

 
Like a Stored Procedure, a Function is also categorized in the following two major categories:
 
system definedTypes of SQL Server Function 
 
System Defined Functions
 
System Defined Functions are those that are pre present in SQL Server for some base or important operations that will be hard to implement without System Defined Functions.
 
So System Defined Functions play an important role in dealing with some complex and rapidly required operations.
 
User Defined Functions
 
As it is clear from the name too, but still we can define User Defined Functions in most common words as:
 
"Functions, that user creates during implementing a query operation for making query easier and faster are called User Defined Functions."
 

Creating SQL Server Function

 
Here's a sample function:
 
SQL Server Function example 
 

Stored Procedure VS Functions

 
In the following table, I am including some base level differences between Stored Procedures and functions that every developer needs to understand because this concept matters a lot in every course of time. So just go through it.
 
Stored Procedure VS Functions 
 

Summary

 
I hope this article was interesting and informative. If this article added a few points to your mind's dictionary regarding SQL Server Stored Procedures and functions then my arrow is accurate.
 
For any query feel free to raise your hand. I'll try my best to sort it out.