Dec 12, 2019 
5.3k
23
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
- SQL Server Blogs -
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: How to Get Your Career as an SSIS Developer Rolling?
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
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
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: SQL Schema | Learn How to Create, Alter and Drop Schema in SQL Effectively!
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
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.
| 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 |
Some example of system- defined stored procedure is
Sp_help
exec sp_helptext [storedproc_test]
Shows the details of a stored procedure provided as parameter
Sp_table
Gives the details of all the tables present in a database
Read: Introduction to SQL Injection and Attacks
EXEC sys.sp_tables
Sp_depends
Gives the dependent object details in multiple rows.
Sp_depends [SalesReason]
Moving ahead, let us discuss the benefits of stored procedures when they are used in the extended form.
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: Know Everything About a SQL Developer Salary for Freshers & Experienced Professionals in 2021!
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
Here is the output
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: Most Popular SQL Server Performance Tuning Tips
You pass the parameter into the stored procedure as below
exec addtwovalue 1,2
The output is as below
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 REPLACE() Function: A Step-By-Step 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
Request for a demo class now to practice stored procedure fundamentals.
SQL Server Training & Certification
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
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
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: How to Ace Your Microsoft Power BI Certification Exam
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
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: How to Become a SQL Database Administrator?
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
Learn creating stored procedures in SQL by joining our online training classes today!
SQL Server Training & Certification
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: Different Types of SQL Server & SQL Database Functions
FaceBook
Twitter
LinkedIn
Pinterest
Email
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.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Jul 09, 2024
893.3k
893.3k