Black Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook  - SCHEDULE CALL

- SQL Server Blogs -

Introduction to Stored Procedures and its benefits



Introduction

Imagine a situation where we have to use the same query again and again or we have a set of queries that need to run again and again at regular intervals. Think of a scenario where you need to incorporate a complex logic into your query before giving out the final output. 

Standard SQL queries do not allow the user to include any conditional statements in them. They only allow users to retrieve or modify data stored inside a database.

Read: Top 97 Data Modeling Interview Questions and How To Answer Them

To apply business logic or apply business logic to store data we need to first get the data, process it and update the database.

The Relational database management system allows developers to create programs to process the data in the database. These are called the Stored Procedure.

For the next few pages of the blog, we will learn about stored procedures, benefits of stored procedures, Power BI Stored Procedures, and their uses in database management.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

What is Stored Procedure?

Have you heard of a Fibonacci number series? A Fibonacci number series is a series where the following output of the series is the sum of the previous two outputs. The output is somewhat like this:

0
1
1
2
3
5
8
13

If you are asked to write logic to provide users an output such as above how would you do that. One easy way of doing it is to write a set of union all statements to give out the output. This is how it would look like

select 0
union all
select 1
union all
select 1
union all
select 2
union all
select 3
union all
select 5
union all
select 8
union all
select 13

The output is

Introduction to Stored Procedures and its benefits

But as you can well understand this is a hardcoded solution and not flexible. What if you were asked to extend the series to 13 rows?

Read: Normalization in SQL | 1NF, 2NF, 3NF and BCNF with Examples

This is where a stored procedure comes into play. Stored procedure is a special feature of a database management system that allows you to extend the functionality of a database by writing logical program blocks. Now let us rewrite the above code into something like below:

Create PROCEDURE Storedproc


AS
BEGIN

declare @x as int;
declare @y as int;
declare @z as int;
declare @k as int;
      set @x=0;
  set @y=1;
  set @z=1;
  print @x;
  print @y; 

while @z <> 15
Begin
    set @k=@y + @x;

print @k;
set @x=@y;
set @y=@k;
set @z=@z+1;
     end;

END
GO

Execute the procedure as

 exec Storedproc

The output is

Introduction to Stored Procedures and its benefits
As you can see just changing the loop counter, you can change the number of rows as output. This is the beauty of stored procedure.

Types of Stored Procedures

System-defined stored procedure Benefits Extended Stored procedure Benefits User-defined Stored Procedure Benefits
Preloaded in the system when SQL Server is installed These are stored procedures that reside in DLLs Stored procedures developed by individual users

System-defined stored procedure

Some example of system- defined stored procedure is

  1. Sp_help
  2. Sp_table
  3. Sp_depends

Sp_help

exec sp_helptext [storedproc_test]

Shows the details of a stored procedure provided as parameter

Introduction to Stored Procedures and its benefits 3

Sp_table

Gives the details of all the tables present in a database

Read: SQL BETWEEN: Retrieve Desired Range of Values with Examples

EXEC sys.sp_tables  

Introduction to Stored Procedures and its benefits 4

Sp_depends

Gives the dependent object details in multiple rows.

Sp_depends [SalesReason]

Extended Stored Procedure

Moving ahead, let us discuss the benefits of stored procedures when they are used in the extended form.

  • To add an extended stored procedure to SQL Server you need to use the sp_addextendedproc system stored procedure.
  • sp_addextendedproc 'xp_sample','xp_sample.dll'
  • To remove an extended stored procedure from SQL Server you need to use 
  • sp_dropextendedproc 'xp_sample','xp_samp

User-defined Stored Procedure

User-defined stored procedures are created by users and customized as per the project need. The benefits of stored procedures when created by users are limitless.

Read: How To Differentiate SQL Server JOIN, IN And EXISTS Clause?

Here we try to write an SP which gives an output somewhat like below.


*
**
***
****
*****

Here is the code

create  PROCEDURE printstar

-- Add the parameters for the stored procedure here


AS
BEGIN
declare @i  int;
declare @k  int;
declare @x varchar(max);
set @i=0;
set @k=0;
print @x;
set @x='';
    while @i <= 5
begin
    while @k <= @i
begin
  set @x=@x + '*';

  set @k=@k +1;

end
print @x;
set @x='';
set @k=0;
set @i=@i+1;

     end

END
GO

You execute it with the following command.

exec printstar

Introduction to Stored Procedures and its benefits 5

Here is the output

Parameterized Stored procedure

Parameterized stored procedures are Stored Procedures where you can pass on some values some outside. Let us take a simple example of adding two numbers using a stored procedure.

The code is as below


create  PROCEDURE addtwovalue

@val1 int,
@val2 int
AS
BEGIN
declare @sumval int;
set @sumval=@val1 + @val2;
print @sumval;
END
GO

You mention the parameters just after the name of the stored procedure. Here @val1 and @val2 are two parameters.

Read: Comparative Study of SQL and NoSQL Databases

You pass the parameter into the stored procedure as below

exec addtwovalue 1,2

The output is as below

Introduction to Stored Procedures and its benefits 6

Stored procedure for insert

A Stored Procedure can be used for the insert operation.

Let us first create a test table and use it to insert a row with parameters.

create table testtab

(


   name varchar(50),

   address varchar(50)

  )

Read: SQL Career Path - Step By Step Microsoft SQL Server Career Guide

Now let us create a stored procedure to insert a record into the table.


CREATE PROCEDURE sp_inst
@name varchar(50),
@address varchar(50)
AS
BEGIN
insert into testtab values(@name,@address);

END
GO

To execute the stored procedure

exec sp_inst 'Testname','Testaddr'

Here is the final output

estname','Testaddr

Request for a demo class now to practice stored procedure fundamentals.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

Stored procedure for update

  • Let us take the same table testtab for the SQL update example.
  • The code for the stored procedure for the update will be as follows.

create PROCEDURE sp_upd
@name varchar(50),
@address varchar(50)
AS
BEGIN
update testtab set address=@address where name=@name

END
GO

To run the Procedure we need

exec sp_upd 'Testname','Testaddr1'

The out put is

Introduction to Stored Procedures and its benefits

Stored procedure for Delete

  • Let us take the same table testtab for the SQL delete example.
  • The code for the stored procedure for deletes will be as follows.

alter PROCEDURE sp_del
@name varchar(50)
AS
BEGIN
delete from testtab where name=@name
END
GO

To run the procedure we need

exec sp_del 'Testname'

The output is

Introduction to Stored Procedures and its benefits 7

Error handling in stored procedures

To catch errors in a stored procedure we use Begin Try End Try and Begin Catch and End Catch block.

Here is an example code on the error handling process. The code deliberately generates a divided by zero error and catch and displays the error using the Try-Catch block.

Read: Step By Step SSAS Tutorial For Beginners

CREATE PROCEDURE usp_GetErrorInfo  

AS  

SELECT  

    ERROR_NUMBER() AS ErrorNumber 
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage;  

GO  

BEGIN TRY  

    -- Generate divide-by-zero error. 
    SELECT 1/0;  

END TRY  

BEGIN CATCH  

    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  

END CATCH;   

The output is

Introduction to Stored Procedures and its benefits 8

Stored Procedure with Out Parameter

We have seen how to send a parameter into a stored procedure. But how would you send back a value from a stored procedure? The next paragraph deals with the out parameter of the stored procedure.

Read: SSIS Package - SSIS DB, Security and Upgrades

Here is how the code looks like.



Create PROCEDURE dbo.uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT

AS
BEGIN

SELECT @AddressCount = count(*) 
FROM AdventureWorks2016.Person.Address 
WHERE City = @City

END

To execute the stored procedure we need to use the following group of statements

declare @rowCount int

exec uspGetAddressCount 'Bothell', @AddressCount = @rowCount output
print @rowcount

The output will look like below
 

@rowcount

Learn creating stored procedures in SQL by joining our online training classes today!

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Summary

In the above write-up, we have tried to give you an overview of different types of stored procedures, benefits of stored procedures, Power BI stored procedures, and how they can be used. It is not a comprehensive guide but this would give the reader a sneak peek on the definition of the stored procedure, stored procedure benefits, types and how to use them.

Read: MSBI Interview Questions & Answers for Fresher, Experienced

SQL Tutorial Overview

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

12 days 14 Dec 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

0 day 02 Dec 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

8 days 10 Dec 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

11 days 13 Dec 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

11 days 13 Dec 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

4 days 06 Dec 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

4 days 06 Dec 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

4 days 06 Dec 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

19 days 21 Dec 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

12 days 14 Dec 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

25 days 27 Dec 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

4 days 06 Dec 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews