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".
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:
Types of Stored Procedure
A Stored Procedure is categorized in these two major categories:
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:
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:
Types of SQL Server Function
Like a Stored Procedure, a Function is also categorized in the following two major categories:
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:
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.
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.