SQL Stored Procedures

A stored procedure is a set of SQL statements saved in the database under a name. Once saved, the procedure can be called (executed) at any time with a single command. Instead of writing the same SQL logic again and again, you write it once, save it, and just call it by name whenever you need it.

Think of a stored procedure like a saved recipe. You write the recipe once and save it. Whenever you want to cook that dish, you just say the recipe name — you do not rewrite the steps every time.

Why Use Stored Procedures?

• Reusability — Write complex SQL once and call it from anywhere.
• Performance — The database compiles the procedure once, making repeated calls faster.
• Security — Grant users permission to run the procedure without giving them direct access to the tables.
• Maintainability — When business logic changes, update only the procedure.

Key Differences Between MySQL and MS SQL Server

The Reference Table

Simple Procedure with No Parameters

MySQL Query

DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM Students;
END //
DELIMITER ;

-- Execute the procedure
CALL GetAllStudents();

MS SQL Server Query

CREATE PROCEDURE GetAllStudents
AS
BEGIN
    SELECT * FROM Students;
END
GO

-- Execute the procedure
EXEC GetAllStudents;

Procedure with Input Parameter

MySQL Query

DELIMITER //
CREATE PROCEDURE GetStudentsByCity(IN cityName VARCHAR(30))
BEGIN
    SELECT StudentName, Age, City, Course
    FROM Students
    WHERE City = cityName;
END //
DELIMITER ;

-- Call with a specific city
CALL GetStudentsByCity('Delhi');
CALL GetStudentsByCity('Mumbai');

MS SQL Server Query

-- Parameters use @ prefix; no IN keyword needed
CREATE PROCEDURE GetStudentsByCity
    @cityName NVARCHAR(30)
AS
BEGIN
    SELECT StudentName, Age, City, Course
    FROM Students
    WHERE City = @cityName;
END
GO

-- Execute with a specific city
EXEC GetStudentsByCity @cityName = 'Delhi';
EXEC GetStudentsByCity @cityName = 'Mumbai';

Procedure with Multiple Input Parameters

MySQL Query

DELIMITER //
CREATE PROCEDURE GetStudentsByCityAndCourse(
    IN cityName   VARCHAR(30),
    IN courseName VARCHAR(30)
)
BEGIN
    SELECT StudentName, Age, City, Course, Fees
    FROM Students
    WHERE City = cityName AND Course = courseName;
END //
DELIMITER ;

CALL GetStudentsByCityAndCourse('Mumbai', 'Science');

MS SQL Server Query

CREATE PROCEDURE GetStudentsByCityAndCourse
    @cityName   NVARCHAR(30),
    @courseName NVARCHAR(30)
AS
BEGIN
    SELECT StudentName, Age, City, Course, Fees
    FROM Students
    WHERE City = @cityName AND Course = @courseName;
END
GO

EXEC GetStudentsByCityAndCourse
    @cityName   = 'Mumbai',
    @courseName = 'Science';

Procedure with Output Parameter

An output parameter sends a calculated value back to the caller after the procedure runs.

MySQL Query

DELIMITER //
CREATE PROCEDURE GetTotalStudents(OUT totalCount INT)
BEGIN
    SELECT COUNT(*) INTO totalCount FROM Students;
END //
DELIMITER ;

-- Capture the output value in a session variable
CALL GetTotalStudents(@total);
SELECT @total AS TotalStudents;

MS SQL Server Query

CREATE PROCEDURE GetTotalStudents
    @totalCount INT OUTPUT
AS
BEGIN
    SELECT @totalCount = COUNT(*) FROM Students;
END
GO

-- Declare a variable, pass it as OUTPUT, then display it
DECLARE @total INT;
EXEC GetTotalStudents @totalCount = @total OUTPUT;
SELECT @total AS TotalStudents;

Procedure with Conditional Logic

MySQL Query

DELIMITER //
CREATE PROCEDURE ClassifyStudent(
    IN  studentFee DECIMAL(8,2),
    OUT category   VARCHAR(20)
)
BEGIN
    IF studentFee < 40000 THEN
        SET category = 'Economy';
    ELSEIF studentFee BETWEEN 40000 AND 55000 THEN
        SET category = 'Standard';
    ELSE
        SET category = 'Premium';
    END IF;
END //
DELIMITER ;

CALL ClassifyStudent(38000, @cat);
SELECT @cat;   -- Result: Economy

CALL ClassifyStudent(45000, @cat);
SELECT @cat;   -- Result: Standard

CALL ClassifyStudent(60000, @cat);
SELECT @cat;   -- Result: Premium

MS SQL Server Query

CREATE PROCEDURE ClassifyStudent
    @studentFee DECIMAL(8,2),
    @category   NVARCHAR(20) OUTPUT
AS
BEGIN
    IF @studentFee < 40000
        SET @category = 'Economy';
    ELSE IF @studentFee BETWEEN 40000 AND 55000
        SET @category = 'Standard';
    ELSE
        SET @category = 'Premium';
END
GO

DECLARE @cat NVARCHAR(20);

EXEC ClassifyStudent @studentFee = 38000, @category = @cat OUTPUT;
SELECT @cat;   -- Result: Economy

EXEC ClassifyStudent @studentFee = 45000, @category = @cat OUTPUT;
SELECT @cat;   -- Result: Standard

EXEC ClassifyStudent @studentFee = 60000, @category = @cat OUTPUT;
SELECT @cat;   -- Result: Premium

Managing Stored Procedures

MySQL Query

-- List all stored procedures in a database
SHOW PROCEDURE STATUS WHERE Db = 'SchoolDB';

-- View the code of a specific procedure
SHOW CREATE PROCEDURE GetAllStudents;

-- Delete a procedure
DROP PROCEDURE IF EXISTS GetAllStudents;

MS SQL Server Query

-- List all stored procedures
SELECT name FROM sys.procedures;

-- View the definition of a procedure
EXEC sp_helptext 'GetAllStudents';

-- Delete a procedure
DROP PROCEDURE IF EXISTS GetAllStudents;

Key Points

• MySQL requires DELIMITER // before the procedure and DELIMITER ; after it. MS SQL Server does not need this — use GO instead.
• MySQL uses CALL to execute a procedure. MS SQL Server uses EXEC.
• MS SQL Server parameters use the @ prefix (e.g., @cityName).
• MySQL uses IN and OUT keywords for parameters. MS SQL Server uses no keyword for input and OUTPUT for output.
• MySQL uses ELSEIF inside procedures. MS SQL Server uses ELSE IF (two words).