Stored Procedure Vs Function in SQL Server

Abhishek Jaiswal

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.

10 Comments

Join the conversation! Your thoughts help the community grow.

Sajeeha Ansar10y ago
awesommmmmmmmmmmmme
vignesh j10y ago
Nice It's good
rkraju m11y ago
http://www.dotnetlearners.com/blogs/view/161/Difference-between-stored-procedure-and-function-in-MS-SQL-server.aspx
pramod Singh11y ago
Difference between Function and Stored Procedure in Single Line of Word------------------------------------------------------------------------------------Function can not changes our Database State but Stored Procedure Can do Anything. Due to This we can not put any SQL Statement in side the Function which modify our database state.Like----insert,update delete,Create etc.
Sudheshwer Rai11y ago
Very Nice Keep it up.
Abhishek Jaiswal11y ago
thanks! :)
Gowtham Rajamanickam11y ago
good one..
Abhishek Jaiswal11y ago
Thanks.. for ur kind review. i'll update it soon. :)
pertti jarva11y ago
Maybe you should have also listed the cons of UDFs, especially the multi statement ones...
Palle Technologies12y ago
http://skillgun.com/sql/stored-procedures/interview-questions-and-answers