What is ASP.NET Stored Procedure?
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:
Developers Table
Id | Full Name | Gender | Stack |
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 |
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 tableINSERT INTO DevelopersVALUES (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 tableSELECT 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.
How to create a stored procedure
To create a stored procedure, you have to use the CREATE PROCEDURE statement (also known as Create Proc).
DELIMITER //CREATE PROCEDURE spGetDevelopers()BEGINselect 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.
How to alter a Stored Procedure
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 PROCEDUREandCREATE PROCEDUREstatements.
DROP PROCEDURE spGetDevelopers;DELIMITER //CREATE PROCEDURE spGetDevelopers()COMMENT 'get developer name and gender from table'BEGINSELECT FullName,Gender from Developers;END //DELIMITER ;ALTER PROCEDURE spGetDevelopersCOMMENT 'initiate the alter statement';CALL spGetDevelopers();DROP PROCEDURE spGetDevelopers;DELIMITER //CREATE PROCEDURE spGetDevelopers()COMMENT 'modify procedure to sort result by fullname'BEGINSELECT FullName,Gender FROM Developers ORDER BY FullName;END //DELIMITER ;CALL spGetDevelopers();
How to delete a stored procedure
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.
Parameterized procedures
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:
Example: the OUT parameter
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 TableCREATE 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 tableINSERT INTO DevelopersVALUES (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 ProcedeureDELIMITER $$CREATE PROCEDURE spGetCountOfFemaleDevsByStack (IN @gender VARCHAR(25), --input parameterIN @stack VARCHAR(25), --input parameterOUT @total INT --output parameter)BEGINSET @total = (SELECT COUNT(FullName) FROM DevelopersWHERE 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:
- @gender and @stack are both input parameters.
- @total is the output parameter that stores the number of developers whose gender and and stack matches the query when it is called.
Example: the INOUT parameter
In the example below, the stored procedure (spUpdateCounter()) accepts one INOUT counterIN incINOUT parameter increases the counter by the value of the inc.
DELIMITER //CREATE PROCEDURE spUpdateCounter(INOUT counter INT,IN inc INT)BEGINSET counter = counter + inc;END //DELIMITER ;-- initialize counterSET @counter = 1;CALL spUpdateCounter(@counter,1); -- 2CALL spUpdateCounter(@counter,2); -- 4CALL spUpdateCounter(@counter,3); -- 7SELECT @counter; -- 7
Specifying parameter names
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, @first and @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.
Specifying parameter direction
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 out or output statement.
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 @total variable.
The data type of this variable must match that of the output parameter receiving it.
Specifying parameter data type
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:
- strings
- numeric
- datetime
Specifying parameter default values
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.
Why use stored procedures?
There are so many reasons why we should use stored procedures over ad hoc SQL queries. We’ll discuss some of them below.
- Execution plan & reusability: Once a stored procedure is created, every call to it will use the same execution plan irrespective of change in the parameter passed. However, this is not the same for ad hoc queries, where the slightest change in the query will result in the creation of a new execution plan.
- Reduces network traffic: Earlier, we said that queries could span over hundreds or even thousands of lines of code. Now, imagine the traffic that will be generated every time you have to execute long queries, as opposed to the few lines of code that will be generated when you call your stored procedure.
- Code Reusability & better maintainability: It is much easier and better to create a stored procedure and use it at several different places within your application, or across several applications, than having to rewrite the same code repeatedly. Imagine how boring and tedious it would be to modify the same query everywhere it is implemented, as opposed to doing it in one place.
- Better Security: Stored Procedures provide security in two ways:
-
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.
