ASP.NET is an open-source web framework that was created by Microsoft for building modern web apps and services with .Net.
A Stored Procedure is a group of Transact-SQL statements. I like to think of it as the equivalent of a Function or Method in programming.
If you ever have to write the same query over and over again, then you can write the query as a stored procedure and save it by its name. Imagine you have a developers table that has Id, Name, Gender, and Stack columns as shown below:
Every time you want to retrieve developer information from the table, such as names and gender, you will have to write the following query.
CREATE TABLE Developers ( ID int NOT NULL AUTO_INCREMENT PRIMARY KEY, FullName varchar(50) NOT NULL, Gender varchar(20), Stack varchar(50) ); -- Add data to the table INSERT INTO Developers VALUES (1, 'Bashir Yesufu','Male','IOS'), (2, 'Omowunmi Kassim', 'Female', '.NET'), (3, 'Ayooluwa Thomas', 'Male', '.NET'), (4, 'Chizaram Okoye', 'Female', '.NET'), (5, 'Fortune Agu', 'Male', 'IOS'); -- Retrieve Names and Gender from table SELECT FullNAme, Gender FROM Developers
Instead of having to do this over and over again, you can wrap this query inside a stored procedure and give it a meaningful name.
It is important that the name of the procedure describes its function.
To create a stored procedure, you have to use the
CREATE PROCEDURE statement (also known as Create Proc).
DELIMITER // CREATE PROCEDURE spGetDevelopers() BEGIN select FullName,Gender from Developers; END // DELIMITER ;
Lets see what’s happening in the code above:
When you execute this command in SQL Server, the stored procedure will be created and saved in the Stored Procedure folder that is located inside the Programmability folder of your database.
And, just like that, we have created our first stored procedure!
The picture below is a snapshot of the same procedure created in Microsoft SQL Server Management Studio.
You might be wondering why we would need to write a procedure for a single query. Initially, I also wondered the same thing too. Although this procedure is simple and doesn’t show procedures as having any significant importance, in reality, procedures could be as long as over a hundred lines of codes and, trust me; you don’t want to repeat such long queries every time you want to perform the same operation.
You may have noticed from our previous example that the names were not sorted. So, let’s now modify the procedure.
To modify the stored procedure, use the
ALTER PROCEDURE statement.
N.B. The syntax above will work perfectly in SQL Server. However, in mysql, you cannot modify a stored procedure using this statement. In fact, to make any change, you must drop and re-create the procedure using the
DROP PROCEDURE spGetDevelopers; DELIMITER // CREATE PROCEDURE spGetDevelopers() COMMENT 'get developer name and gender from table' BEGIN SELECT FullName,Gender from Developers; END // DELIMITER ; ALTER PROCEDURE spGetDevelopers COMMENT 'initiate the alter statement'; CALL spGetDevelopers(); DROP PROCEDURE spGetDevelopers; DELIMITER // CREATE PROCEDURE spGetDevelopers() COMMENT 'modify procedure to sort result by fullname' BEGIN SELECT FullName,Gender FROM Developers ORDER BY FullName; END // DELIMITER ; CALL spGetDevelopers();
To delete a procedure, right-click the stored procedure you want to delete and select
Drop Stored Procedure. Alternatively, you can use the
DROP PROCEDURE statement. This is shown below:
--Use "IF EXISTS" if you're not sure that the procedure exists-- DROP PROCEDURE IF EXISTS spGetDevelopers; --Show warnings if procedure does not exist-- SHOW WARNINGS;
N.B. I have used the
IF EXISTSstatement because MySQL will issue an error if the procedure does not exist. However, with this statement, MySQL will issue a warning instead.
Just like Functions in programming languages, procedures can also be parameterized.
Parameters are data that the procedure invokes each time it is called.
There are three types of parameters:
Let’s say we want to query our Developers table to get the count of female developers in .NET stack. To achieve this, we’ll supply both input and output parameters to our procedure.
Let’s see this in the code:
-- Create Database Table CREATE TABLE Developers ( ID int NOT NULL AUTO_INCREMENT PRIMARY KEY, FullName varchar(50) NOT NULL, Gender varchar(20), Stack varchar(50) ); -- Add data to the table INSERT INTO Developers VALUES (1, 'Bashir Yesufu','Male','IOS'), (2, 'Omowunmi Kassim', 'Female', '.NET'), (3, 'Ayooluwa Thomas', 'Male', '.NET'), (4, 'Chizaram Okoye', 'Female', '.NET'), (5, 'Fortune Agu', 'Male', 'IOS'); -- Create Procedeure DELIMITER $$ CREATE PROCEDURE spGetCountOfFemaleDevsByStack ( IN @gender VARCHAR(25), --input parameter IN @stack VARCHAR(25), --input parameter OUT @total INT --output parameter ) BEGIN SET @total = (SELECT COUNT(FullName) FROM Developers WHERE Gender = @gender AND Stack = @stack); END$$ DELIMITER ; --Call procedure with required parameters-- CALL spGetCountOfFemaleDevsByStack('Female', '.NET', @total); SELECT @total AS total_number_of_female_devs_in_dotnet
The stored procedure,
spGetCountOfFemaleDevsByStack(), has three parameters:
In the example below, the stored procedure (
spUpdateCounter()) accepts one
INOUT parameter increases the
counter by the value of the
DELIMITER // CREATE PROCEDURE spUpdateCounter( INOUT counter INT, IN inc INT ) BEGIN SET counter = counter + inc; END // DELIMITER ; -- initialize counter SET @counter = 1; CALL spUpdateCounter(@counter,1); -- 2 CALL spUpdateCounter(@counter,2); -- 4 CALL spUpdateCounter(@counter,3); -- 7 SELECT @counter; -- 7
By convention, parameters begin with an
@ character and must be unique within the scope of the procedure.
When calling a procedure, you must follow the order in which the parameters were defined upon creation. However, if you wish to call them in no particular order, then you must name them explicitly. For example, if your procedure expects two parameters,
@second, the values must be passed in that specific order. You could name them explicitly, such as:
@second = value,
@first = value.
If one parameter value is explicitly defined (e.g.,
@parameter = value), all subsequent parameters must be defined in the same manner.
The direction of a parameter can either be an input or an output. By default, a parameter is input type unless it is explicitly defined with the
From the example above, the procedure expects that we pass in a gender and a stack. After the call statement, the query will count the number of female developers in .NET and pass the value into the
The data type of this variable must match that of the output parameter receiving it.
The data type of a parameter must be declared when a procedure is created because it determines the type and range of values that are accepted when the procedure is called.
If you execute a procedure with a value that isn’t compatible with the data type, an error will be returned.
There are three main data types in MySql:
Visit this link for more information about data types.
To create a default value for a procedure, we have to assign a value to the parameter upon creation. If you do not pass in a value for a parameter, the default value will be used. Let’s refactor our last example by adding a default value to gender.
MySQL does not support default parameters.
There are so many reasons why we should use stored procedures over ad hoc SQL queries. We’ll discuss some of them below.
Data Encapsulation: by hiding the implementation, we can have final grain control over the data other users can see.
Avoid SQL injection attack: stored procedures provide better security by preventing users from injecting SQL queries from the Frontend that could alter the data in our database.
So far, we’ve defined ASP.NET and Stored Procedures. We’ve also discussed how Procedures are created as well as highlighted their importance. Now, let’s put everything together.
For an illustration, visit this link to my GitHub repository. There I have a practical illustration of how procedures can be used on an asp.net web project. Please check the readme file for any help you might need.
View all Courses