a shot of dev knowledge

RELATED TAGS

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 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.

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()
BEGIN
    select FullName,Gender from Developers;
END //
    
DELIMITER ;

Lets see what’s happening in the code above:

  • Just as the name implies, the Create command is used to create a stored procedure.
  • spGetDevelopers is the name I have chosen. By convention, the name should start with sp and be followed by whatever you wish to call it.
  • 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 PROCEDURE and CREATE PROCEDURE statements.

    main.sql
    modify_main.sql
    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();

    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 EXISTS statement 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:

  • Input (IN) parameter
  • Output (OUT) parameter
  • InOut parameter
  • 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 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:

    • @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 parametercounter and one IN parameterinc. The INOUT parameter increases the counter by the value of the inc.

    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

    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

    Visit this link for more information about data types.

    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.

    1. 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.
    2. 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.
    3. 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.
    4. 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.

    Putting everything together

    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.

    RELATED TAGS

    RELATED COURSES

    View all Courses

    Keep Exploring