SQL Stored Procedure: Syntax, Usage, and Examples

An SQL stored procedure is a precompiled set of SQL statements stored in the database. It allows for reusable, modular code that improves performance and simplifies complex database operations. Stored procedures can take parameters, execute queries, and return results.

How to Use an SQL Stored Procedure

A stored procedure is created using the CREATE PROCEDURE statement and executed with the EXEC or CALL command, depending on the database system.

Creating a Stored Procedure

CREATE PROCEDURE GetAllUsers()
AS
BEGIN
    SELECT * FROM users;
END;

Executing a Stored Procedure

In SQL Server:

EXEC GetAllUsers;

In MySQL and PostgreSQL:

CALL GetAllUsers();

Stored Procedure with Parameters

Stored procedures can accept input parameters for dynamic queries.

CREATE PROCEDURE GetUserByID(@UserID INT)
AS
BEGIN
    SELECT * FROM users WHERE id = @UserID;
END;

To execute it:

EXEC GetUserByID 5;

This retrieves the user with id = 5.

When to Use an SQL Stored Procedure

Stored procedures improve performance and code reusability while ensuring secure database interactions.

Reusable Business Logic

Instead of writing the same SQL query multiple times, store frequently used queries in a stored procedure.

CREATE PROCEDURE GetActiveUsers()
AS
BEGIN
    SELECT * FROM users WHERE status = 'active';
END;

Now, any part of the application can call EXEC GetActiveUsers instead of rewriting the query.

Transaction Management

Stored procedures help maintain data consistency in complex transactions.

CREATE PROCEDURE TransferFunds(@FromAccount INT, @ToAccount INT, @Amount DECIMAL(10,2))
AS
BEGIN
    BEGIN TRANSACTION;

    UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;
    UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccount;

    COMMIT;
END;

If one update fails, the entire transaction rolls back.

Improved Security

Using stored procedures restricts direct database access. Applications can call the procedure without having permission to modify tables directly.

GRANT EXECUTE ON GetUserByID TO app_user;

This allows app_user to call GetUserByID but not run SELECT * FROM users directly.

Examples of SQL Stored Procedures

Inserting Data Using a Stored Procedure

CREATE PROCEDURE AddUser(@Name VARCHAR(50), @Email VARCHAR(100))
AS
BEGIN
    INSERT INTO users (name, email) VALUES (@Name, @Email);
END;

Execute it:

EXEC AddUser 'Alice', 'alice@example.com';

Updating Data Using a Stored Procedure

CREATE PROCEDURE UpdateUserEmail(@UserID INT, @NewEmail VARCHAR(100))
AS
BEGIN
    UPDATE users SET email = @NewEmail WHERE id = @UserID;
END;

Execute it:

EXEC UpdateUserEmail 5, 'newemail@example.com';

Deleting Data Using a Stored Procedure

CREATE PROCEDURE DeleteUser(@UserID INT)
AS
BEGIN
    DELETE FROM users WHERE id = @UserID;
END;

Execute it:

EXEC DeleteUser 10;

Learn More About SQL Stored Procedures

Stored Procedures vs. Regular Queries

Stored procedures have advantages over regular queries:

• Performance: The database compiles and optimizes stored procedures once, whereas regular queries run from scratch each time.
• Security: Users can execute a stored procedure without direct table access.
• Code Reusability: Procedures eliminate the need to rewrite complex queries in different places.

Returning Values from Stored Procedures

Some stored procedures return values, often using OUTPUT parameters.

CREATE PROCEDURE GetUserCount(@TotalUsers INT OUTPUT)
AS
BEGIN
    SELECT @TotalUsers = COUNT(*) FROM users;
END;

To call it:

DECLARE @UserCount INT;
EXEC GetUserCount @UserCount OUTPUT;
PRINT @UserCount;

Handling Errors in Stored Procedures

Use TRY...CATCH blocks to handle errors.

CREATE PROCEDURE SafeTransfer(@FromAccount INT, @ToAccount INT, @Amount DECIMAL(10,2))
AS
BEGIN
    BEGIN TRANSACTION;

    BEGIN TRY
        UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;
        UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccount;
        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        PRINT 'Error occurred. Transaction rolled back.';
    END CATCH;
END;

If an error occurs, ROLLBACK ensures that no changes are made.

Nested Stored Procedures

A stored procedure can call another stored procedure.

CREATE PROCEDURE GetUserDetails(@UserID INT)
AS
BEGIN
    EXEC GetUserByID @UserID;
END;

This allows modular execution of stored procedures.

Stored Procedures with Loops

Stored procedures can include loops, useful for batch processing.

CREATE PROCEDURE PrintNumbers(@Max INT)
AS
BEGIN
    DECLARE @Counter INT = 1;
    WHILE @Counter <= @Max
    BEGIN
        PRINT @Counter;
        SET @Counter = @Counter + 1;
    END;
END;

Executing EXEC PrintNumbers 5; prints numbers from 1 to 5.