Backend

Create a Stored Procedure with a series of stored procedures

We’re going to cover on how to write a stored procedure with a series of stored procedures. That means running a number of stored procedures within one stored procedure.

You might do this for effiency and to avoid duplicating your SELECT/INSERT queries. We will create Stored Procedures to return primary keys to be subsequently used in SELECT or INSERT queries.

We’ll be using SQL Server 2017. The version of SQL Server version does not matter as long as it’s a recent version.

  1. Create or use a test database of your choice. I’m using a test database called Example Database
  2. Run the SQL query below to create new tables; Country, Town and Branch
USE [ExampleDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Country]( [CountryID] [int] IDENTITY(1,1) NOT NULL, [CountryName] <a href="120">varchar</a> NOT NULL, CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED ( [CountryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Town]( [TownID] [int] IDENTITY(1,1) NOT NULL, [TownName] <a href="120">varchar</a> NOT NULL, [CountryID] [int] NOT NULL, CONSTRAINT [PK_Town] PRIMARY KEY CLUSTERED ( [TownID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Branch]( [BranchID] [int] IDENTITY(1,1) NOT NULL, [BranchName] <a href="120">varchar</a> NOT NULL, [TownID] [int] NOT NULL, [CountryID] [int] NOT NULL, CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ( [BranchID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Code language: SQL (Structured Query Language) (sql)

3. We’ll create 3 new Stored Procedures. Look up “Programmability” node in your database and right click “Stored Procedure…”. This will create a new Query window.

We’ll add a new Stored Procedure to add a new Country. Remember to change name of the database [ExampleDatabase] to your name of database.

USE [ExampleDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddCountry] @CountryName varchar(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @CountryID as int = 0; IF EXISTS(SELECT * FROM [dbo].[Country] WHERE CountryName LIKE @CountryName) BEGIN SELECT @CountryID=CountryID FROM [dbo].[Country] WHERE CountryName LIKE @CountryName END ELSE BEGIN INSERT INTO [dbo].[Country] (CountryName) VALUES (@CountryName); SELECT @CountryID = @@IDENTITY END RETURN @CountryID END
Code language: SQL (Structured Query Language) (sql)

The above Stored Procedure accepts one parameter, CountryName. It checks if the CountryName alreadys exists in the table. If it doesn’t, it creates a new Country and gets the latest primary key and stores this in the variable ID. Otherwise, it will get the Primary Key from the existing record

USE [ExampleDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddTown] @TownName varchar(MAX), @CountryID as int AS BEGIN SET NOCOUNT ON; DECLARE @TownID as int = 0; IF NOT EXISTS(SELECT * FROM [dbo].[Town] WHERE TownName LIKE @TownName AND CountryID=@CountryID) BEGIN INSERT INTO [dbo].[Town] ([TownName],[CountryID]) VALUES (@TownName, @CountryID); SELECT @TownID = @@IDENTITY END ELSE BEGIN SELECT @TownID=TownID FROM [dbo].[Town] WHERE TownName LIKE @TownName AND CountryID=@CountryID END RETURN @TownID END
Code language: SQL (Structured Query Language) (sql)


The Stored Procedure accepts two parameters, TownName and CountryID. It will check if the record already exists by querying for the TownName and CountryID. You can get same town names but in different countries. We want to avoid creating duplicate records. If a record doesn’t exist, it will create a new record with TownName and the CountryID. By adding the CountryID to the Town record, we are creating a Foreign Key where we can link Towns to Countries.

If a Town record exist, it will get the primary key, TownID, from the table Town.

One last Stored Procedure to add. In this one, we’ll create a new Branch record. This record can be a branch part of your chain of shops like coffee, clothes, or fast food.

USE [ExampleDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddBranch] @BranchName as varchar(MAX), @TownName as varchar(MAX), @CountryName as varchar(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @BranchID as int = 0; DECLARE @CountryID as int = 0; DECLARE @TownID as int = 0; -- GET COUNTRY ID EXEC @CountryID = AddCountry @CountryName; -- GET TOWN ID EXEC @TownID = AddTown @TownName, @CountryID; -- GET BRANCH ID IF EXISTS(SELECT * FROM [dbo].[Branch] WHERE BranchName LIKE @BranchName AND CountryID=@CountryID AND TownID=@TownID) BEGIN SELECT @BranchID=BranchID FROM [dbo].[Branch] WHERE BranchName LIKE @BranchName AND CountryID=@CountryID AND TownID=@TownID END ELSE BEGIN INSERT INTO [dbo].[Branch] (BranchName, TownID, CountryID) VALUES (@BranchName, @TownID, @CountryID); SELECT @BranchID = @@IDENTITY END END
Code language: SQL (Structured Query Language) (sql)

The above Stored Procedures has two Stored Procedures ready to be executed. Those Stored procedures will return CountryID and TownID which we can subsequently use to query for existing Branch records. If none exists, we can create a new Branch record with the branch name, Town ID and Country ID. It has logic checks added to prevent duplicate records from being built.

4. Time to test the Stored Procedure

USE [ExampleDatabase]; -- DELETE ALL RECORDS TRUNCATE TABLE Town; TRUNCATE TABLE Country; TRUNCATE TABLE Branch; exec AddBranch 'Branch Alpha', 'Glasgow', 'Scotland' exec AddBranch 'Branch Beta', 'Edinburgh', 'Scotland' exec AddBranch 'Branch Charlie', 'Manchester', 'England' exec AddBranch 'Branch Delta', 'Liverpool', 'England' exec AddBranch 'Branch Echo', 'Cardiff', 'Wales' exec AddBranch 'Branch Fox', 'Swansea', 'Wales' -- GET ALL BRANCHES SELECT b.BranchID, b.BranchName, t.TownID, t.TownName, c.CountryID, CountryName FROM Branch as b INNER JOIN Town as t ON t.TownID=b.TownID INNER JOIN Country as c ON c.CountryID=b.CountryID
Code language: SQL (Structured Query Language) (sql)

After executing the Stored Procedure to add new Branches, it will then run a query to get all branches. It will list the Branch name, Town and Country details.

In the Stored Procedure AddBranch, it will create or find Town and Country records. Then those records IDs are used to create a new Branch if it doesn’t already exists.