Stored Procedure Vs Function in SQL Server

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.

Outline

• Overview
  
• Stored Procedure
  
  • Stored Procedure | Pros
  • Stored Procedure | Hierarchy
  • Stored Procedure | Type
    
    
    - System Stored Procedure
    - User Stored Procedure
    
    
  • Stored Procedure | Creation
    
    
• Functions
• S-Proc vs Functions
  
  
  • Function | Pros
  • Function | Hierarchy
  • Function | Type
    
    
    - System Defined
    - User Defined
    
    
  • Function | Creation
    
    
• Summary

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 maintain 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

1. Stored Procedure

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 oftenly called a "S-Proc"or "SP".



Stored Procedure | Pros

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 have both input and output parameters
      - Cab be used with XML for clause
      - Used in changing server configuration
      - SP allows DML statements as well as Selects

Stored Procedure | Hierarchy

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



Stored Procedure | Types

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 the three parts:

• User Stored Procedure
• User Defined Functions
• Triggers

Stored Procedure | Creation

Here's a SProc example:



Credits: #MSDN

2. Function

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.

Functions | Pros

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

Function | Hierarchy

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



Function | Type

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."

Function | Creation

Here's sample function:



SProc. 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 coarse 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.