SQL Data Interview Series: Stored Procedures in SQL

Image from LearnSQL

Hey there everyone. Welcome to Day 5 of the SQL Data Analyst Interview Questions series. You can read up yesterday’s topic — Subqueries in SQL — here. Today, we’ll be discussing stored procedures in SQL, in response to Question 26 of Jessica Ayodele’s Data Analyst Interview Questions — ‘What are stored procedures in SQL?’ In this, we’ll be talking all about stored procedures, stored procedures with parameters, and uses of stored procedures.
Let’s get to it.

What is a Stored Procedure?

A stored procedure is a group of SQL Statements which can be created, saved in the database, and executed whenever it is needed, thereby reducing & saving time on repetitive tasks and increasing efficiency.

Let’s make use of our SQL for Marketing dataset. We want to get the details of our highest-spending customers, i.e. their IDs, education levels, relationship status, amounts spent, number of campaign offers accepted, etc.

As we can see from the above image, this query is quite lengthy. Now imagine having to input this every single time you need this information. Geez! Not only does it waste your time, it also takes up extra space in the database. But, what if I told you of the easier way to get this done? Take a guess? You guessed right: By making use of stored procedures.

We’ll wrap up that lengthy query in a stored procedure, so next time all we need do is just execute the stored procedure.

CREATE PROCEDURE CUST
AS
SELECT Id, Income, Education, Age_Rank, Partner,
 SUM(Kidhome) + SUM(Teenhome) Kids,
 SUM(AcceptedCmp1) + SUM(AcceptedCmp2) + SUM(AcceptedCmp3) + SUM(AcceptedCmp4) + SUM(AcceptedCmp5) CmpSum,
 SUM(MntWines) + SUM(MntFruits) + SUM(MntMeatProducts) + SUM(MntFishProducts) + SUM(MntSweetProducts) + SUM(MntGoldProds) AmtPurchase
FROM SQLDevotee.dbo.Marketing_Campaign
GROUP BY Id, Income,Education, Age_Rank, Partner
ORDER BY AmtPurchase DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

EXEC CUST

Instead of typing out that query, we’ll just execute the stored procedure ‘CUST’.

Stored Procedure with Parameters

A parameter in a stored procedure is a condition given by the user, e.g. in our stored procedure, we have a column ‘Partner’. Now, let’s say we the users want to only view customers that have partners, i.e. where Partner = ‘Yes’. We’ll input this condition into our stored procedure and execute it.

CREATE PROCEDURE CUSTPartner
@Partner nvarchar(255)
AS
SELECT Id, Income, Education, Age_Rank, Partner,
 SUM(Kidhome) + SUM(Teenhome) Kids,
 SUM(AcceptedCmp1) + SUM(AcceptedCmp2) + SUM(AcceptedCmp3) + SUM(AcceptedCmp4) + SUM(AcceptedCmp5) CmpSum,
 SUM(MntWines) + SUM(MntFruits) + SUM(MntMeatProducts) + SUM(MntFishProducts) + SUM(MntSweetProducts) + SUM(MntGoldProds) AmtPurchase
FROM SQLDevotee.dbo.Marketing_Campaign
WHERE Partner = @Partner
GROUP BY Id, Income, Education, Age_Rank, Partner
ORDER BY AmtPurchase DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

EXEC CUSTPartner @Partner = 'Yes'

This stored procedure has a parameter — Partner — which when executed gives us the result of whatever conditions the user wants fulfilled, i.e. Partner = ‘Yes’ gives us customers with partners; Partner = ‘No’ gives us customers without partners.

That’s for a single parameter. Now, let’s create a subquery with multiple parameters.

CREATE PROCEDURE CUSTMultiple
@Partner nvarchar(255),
@Education nvarchar(255),
@Age_Rank nvarchar(255)
AS
SELECT Id, Income, Education, Age_Rank, Partner,
 SUM(Kidhome) + SUM(Teenhome) Kids,
 SUM(AcceptedCmp1) + SUM(AcceptedCmp2) + SUM(AcceptedCmp3) + SUM(AcceptedCmp4) + SUM(AcceptedCmp5) CmpSum,
 SUM(MntWines) + SUM(MntFruits) + SUM(MntMeatProducts) + SUM(MntFishProducts) + SUM(MntSweetProducts) + SUM(MntGoldProds) AmtPurchase
FROM SQLDevotee.dbo.Marketing_Campaign
WHERE Partner = @Partner
AND Education = @Education
AND Age_Rank = @Age_Rank
GROUP BY Id, Income, Education, Age_Rank, Partner
ORDER BY AmtPurchase DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

This subquery has three parameters: Partner, Education, and Age Rank. Supposing we’d like to find out customers without partners who have a first degree and are young.

Advantages of Stored Procedures

• Increased performance
• Retention and reusability
• Reduction of network traffic
• Better security

Differences between Stored Procedures and Views

For differences between stored procedures and views, read up our Day 2’s discussion on views.

And that’s it today on Stored Procedures in SQL. Hope you learnt a thing or four. If you did — I know you did — give this lots of claps and leave lovely encouraging comments for your good ol’ favourite Data Explorer 😊. Tomorrow, we’ll discuss String Functions in SQL. 🤗

GIF from GIPHY