SQL Stored Procedures: Complete Guide with Examples (2026)

What Are Stored Procedures and Why Use Them

A stored procedure is a precompiled collection of SQL statements saved on the database server. Instead of sending multiple queries from your application, you call a single procedure that executes all the logic on the server side.

Think of it as a function for your database. You define it once, and any application connected to that database can call it by name. The database engine parses and compiles the procedure when you create it, then caches the execution plan for subsequent calls.

There are several reasons stored procedures remain a core part of database development:

• Performance. The execution plan is compiled once and reused. Repeated calls skip the parsing and optimization steps, which matters for queries that run thousands of times per minute.
• Reduced network traffic. Instead of sending five separate SQL statements from your application to the database, you send one procedure call. The database handles the rest internally.
• Security. You can grant users permission to execute a procedure without giving them direct access to the underlying tables. This limits what they can do and prevents accidental data modifications.
• Consistency. Business logic lives in one place. If three applications need to register a user the same way, they all call the same procedure instead of duplicating the logic.
• Maintainability. Changing the logic inside a procedure does not require redeploying application code. You alter the procedure on the server and every caller gets the updated behavior immediately.

Stored procedures are supported by every major relational database: MySQL, PostgreSQL, SQL Server, Oracle, and MariaDB. The syntax differs between them, but the concepts are the same.

Creating Stored Procedures

The basic structure is similar across databases: you define a name, optional parameters, and a body containing SQL statements. Here is the syntax for each major platform.

MySQL

DELIMITER //

CREATE PROCEDURE GetActiveUsers()
BEGIN
    SELECT id, name, email, last_login
    FROM users
    WHERE is_active = 1
    ORDER BY last_login DESC;
END //

DELIMITER ;

-- Call the procedure
CALL GetActiveUsers();

MySQL requires DELIMITER changes because the procedure body contains semicolons that would otherwise end the CREATE statement prematurely.

PostgreSQL

CREATE OR REPLACE PROCEDURE get_active_users()
LANGUAGE plpgsql
AS $$
BEGIN
    -- In PostgreSQL, procedures use CALL and cannot return result sets directly.
    -- Use a function with RETURNS TABLE for SELECT results.
    RAISE NOTICE 'Use get_active_users_func() for result sets';
END;
$$;

-- PostgreSQL function returning a result set
CREATE OR REPLACE FUNCTION get_active_users_func()
RETURNS TABLE(id INT, name VARCHAR, email VARCHAR, last_login TIMESTAMP)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.name, u.email, u.last_login
    FROM users u
    WHERE u.is_active = true
    ORDER BY u.last_login DESC;
END;
$$;

-- Call the function
SELECT * FROM get_active_users_func();

PostgreSQL added CREATE PROCEDURE in version 11. Before that, everything was done with functions. In practice, PostgreSQL developers still use functions for anything that returns data, and procedures for transactional operations.

SQL Server

CREATE PROCEDURE GetActiveUsers
AS
BEGIN
    SET NOCOUNT ON;

    SELECT id, name, email, last_login
    FROM users
    WHERE is_active = 1
    ORDER BY last_login DESC;
END;
GO

-- Call the procedure
EXEC GetActiveUsers;

SET NOCOUNT ON prevents SQL Server from returning the count of affected rows, which reduces unnecessary network traffic and avoids confusion in applications that parse result sets.

Parameters: IN, OUT, and INOUT

Parameters let you pass values into a procedure and receive values back. There are three types.

IN Parameters (Input)

These are the default. The caller passes a value, and the procedure reads it.

-- MySQL
CREATE PROCEDURE GetUsersByCountry(IN country_name VARCHAR(100))
BEGIN
    SELECT id, name, email
    FROM users
    WHERE country = country_name;
END;

CALL GetUsersByCountry('Germany');

-- SQL Server
CREATE PROCEDURE GetUsersByCountry
    @country_name NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, name, email
    FROM users
    WHERE country = @country_name;
END;

EXEC GetUsersByCountry @country_name = 'Germany';

OUT Parameters (Output)

These return a value to the caller without using a result set.

-- MySQL
CREATE PROCEDURE CountUsersByCountry(
    IN country_name VARCHAR(100),
    OUT user_count INT
)
BEGIN
    SELECT COUNT(*) INTO user_count
    FROM users
    WHERE country = country_name;
END;

CALL CountUsersByCountry('Germany', @total);
SELECT @total;

-- SQL Server
CREATE PROCEDURE CountUsersByCountry
    @country_name NVARCHAR(100),
    @user_count INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @user_count = COUNT(*)
    FROM users
    WHERE country = @country_name;
END;

DECLARE @total INT;
EXEC CountUsersByCountry 'Germany', @total OUTPUT;
SELECT @total;

INOUT Parameters

These accept an input value and return a modified value through the same parameter. They exist in MySQL and PostgreSQL but not in SQL Server (use OUTPUT parameters that also accept input values instead).

-- MySQL
CREATE PROCEDURE ApplyDiscount(INOUT price DECIMAL(10,2), IN discount_pct INT)
BEGIN
    SET price = price - (price * discount_pct / 100);
END;

SET @item_price = 100.00;
CALL ApplyDiscount(@item_price, 15);
SELECT @item_price;  -- Returns 85.00

Control Flow: IF, WHILE, LOOP, CASE

Stored procedures support conditional logic and loops, which is where they become significantly more powerful than standalone SQL statements.

IF / ELSEIF / ELSE

-- MySQL
CREATE PROCEDURE CategorizeUser(IN user_id INT)
BEGIN
    DECLARE total_orders INT;
    DECLARE user_tier VARCHAR(20);

    SELECT COUNT(*) INTO total_orders
    FROM orders WHERE customer_id = user_id;

    IF total_orders >= 50 THEN
        SET user_tier = 'platinum';
    ELSEIF total_orders >= 20 THEN
        SET user_tier = 'gold';
    ELSEIF total_orders >= 5 THEN
        SET user_tier = 'silver';
    ELSE
        SET user_tier = 'bronze';
    END IF;

    UPDATE users SET tier = user_tier WHERE id = user_id;
END;

-- SQL Server
CREATE PROCEDURE CategorizeUser @user_id INT
AS
BEGIN
    DECLARE @total_orders INT;
    DECLARE @user_tier NVARCHAR(20);

    SELECT @total_orders = COUNT(*)
    FROM orders WHERE customer_id = @user_id;

    IF @total_orders >= 50
        SET @user_tier = 'platinum';
    ELSE IF @total_orders >= 20
        SET @user_tier = 'gold';
    ELSE IF @total_orders >= 5
        SET @user_tier = 'silver';
    ELSE
        SET @user_tier = 'bronze';

    UPDATE users SET tier = @user_tier WHERE id = @user_id;
END;

CASE Statement

-- Works in MySQL, PostgreSQL, SQL Server
CREATE PROCEDURE GetShippingRate(
    IN weight_kg DECIMAL(10,2),
    OUT rate DECIMAL(10,2)
)
BEGIN
    SET rate = CASE
        WHEN weight_kg <= 1 THEN 5.99
        WHEN weight_kg <= 5 THEN 12.99
        WHEN weight_kg <= 20 THEN 24.99
        ELSE 49.99
    END;
END;

WHILE Loop

-- MySQL: Insert 1000 test records
CREATE PROCEDURE GenerateTestData()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 1000 DO
        INSERT INTO test_users (name, email)
        VALUES (
            CONCAT('User_', i),
            CONCAT('user', i, '@test.com')
        );
        SET i = i + 1;
    END WHILE;
END;

LOOP with LEAVE

-- MySQL: Process until condition met
CREATE PROCEDURE ProcessQueue()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE task_id INT;

    process_loop: LOOP
        SELECT id INTO task_id
        FROM task_queue
        WHERE status = 'pending'
        ORDER BY created_at
        LIMIT 1;

        IF task_id IS NULL THEN
            LEAVE process_loop;
        END IF;

        UPDATE task_queue SET status = 'processing' WHERE id = task_id;
        -- Process the task here
        UPDATE task_queue SET status = 'completed' WHERE id = task_id;
    END LOOP;
END;

Error Handling

Production stored procedures need error handling. Without it, a failure midway through a multi-step operation leaves your data in an inconsistent state.

SQL Server: TRY/CATCH

CREATE PROCEDURE TransferFunds
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE accounts
        SET balance = balance - @amount
        WHERE id = @from_account;

        IF @@ROWCOUNT = 0
            THROW 50001, 'Source account not found', 1;

        UPDATE accounts
        SET balance = balance + @amount
        WHERE id = @to_account;

        IF @@ROWCOUNT = 0
            THROW 50002, 'Destination account not found', 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;

PostgreSQL: EXCEPTION Block

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account INT,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    rows_affected INT;
BEGIN
    UPDATE accounts
    SET balance = balance - amount
    WHERE id = from_account;

    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    IF rows_affected = 0 THEN
        RAISE EXCEPTION 'Source account % not found', from_account;
    END IF;

    UPDATE accounts
    SET balance = balance + amount
    WHERE id = to_account;

    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    IF rows_affected = 0 THEN
        RAISE EXCEPTION 'Destination account % not found', to_account;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transfer failed: %', SQLERRM;
        RAISE;
END;
$$;

MySQL: DECLARE HANDLER

CREATE PROCEDURE SafeInsertUser(
    IN user_email VARCHAR(255),
    OUT result VARCHAR(50)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR 1062
    BEGIN
        SET result = 'duplicate_email';
    END;

    SET result = 'success';

    INSERT INTO users (email, created_at)
    VALUES (user_email, NOW());
END;

Error code 1062 in MySQL is a duplicate key violation. The handler catches it and sets a meaningful result instead of throwing an error to the caller.

Real-World Examples

Here are three practical stored procedures that solve common application problems.

User Registration with Validation

-- SQL Server
CREATE PROCEDURE RegisterUser
    @email NVARCHAR(255),
    @password_hash NVARCHAR(255),
    @name NVARCHAR(100),
    @result NVARCHAR(50) OUTPUT,
    @new_user_id INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Check if email already exists
    IF EXISTS (SELECT 1 FROM users WHERE email = @email)
    BEGIN
        SET @result = 'email_exists';
        SET @new_user_id = NULL;
        RETURN;
    END;

    -- Validate email format (basic check)
    IF @email NOT LIKE '%_@_%.__%'
    BEGIN
        SET @result = 'invalid_email';
        SET @new_user_id = NULL;
        RETURN;
    END;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Insert the user
        INSERT INTO users (email, password_hash, name, created_at, is_active)
        VALUES (@email, @password_hash, @name, GETDATE(), 1);

        SET @new_user_id = SCOPE_IDENTITY();

        -- Create default settings
        INSERT INTO user_settings (user_id, theme, language, notifications_enabled)
        VALUES (@new_user_id, 'light', 'en', 1);

        -- Log the registration
        INSERT INTO audit_log (event_type, user_id, details, created_at)
        VALUES ('user_registered', @new_user_id, 'New user registration', GETDATE());

        COMMIT TRANSACTION;
        SET @result = 'success';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        SET @result = 'error';
        SET @new_user_id = NULL;
    END CATCH;
END;

Monthly Report Generation

-- MySQL
CREATE PROCEDURE GenerateMonthlyReport(IN report_month DATE)
BEGIN
    DECLARE month_start DATE;
    DECLARE month_end DATE;

    SET month_start = DATE_FORMAT(report_month, '%Y-%m-01');
    SET month_end = LAST_DAY(report_month);

    -- Clear previous report for this month
    DELETE FROM monthly_reports WHERE report_date = month_start;

    -- Generate and insert the report
    INSERT INTO monthly_reports (
        report_date, total_revenue, total_orders,
        new_customers, avg_order_value, top_product_id
    )
    SELECT
        month_start,
        COALESCE(SUM(o.total_amount), 0),
        COUNT(DISTINCT o.id),
        (SELECT COUNT(*) FROM users
         WHERE created_at BETWEEN month_start AND month_end),
        COALESCE(AVG(o.total_amount), 0),
        (SELECT product_id FROM order_items oi
         JOIN orders o2 ON oi.order_id = o2.id
         WHERE o2.order_date BETWEEN month_start AND month_end
         GROUP BY product_id
         ORDER BY SUM(oi.quantity) DESC
         LIMIT 1)
    FROM orders o
    WHERE o.order_date BETWEEN month_start AND month_end
      AND o.status = 'completed';
END;

Data Cleanup with Batch Processing

-- SQL Server: Delete old records in batches to avoid lock escalation
CREATE PROCEDURE CleanupOldLogs
    @days_to_keep INT = 90,
    @batch_size INT = 5000,
    @total_deleted INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cutoff_date DATETIME = DATEADD(DAY, -@days_to_keep, GETDATE());
    DECLARE @batch_deleted INT = 1;
    SET @total_deleted = 0;

    WHILE @batch_deleted > 0
    BEGIN
        DELETE TOP (@batch_size)
        FROM application_logs
        WHERE created_at < @cutoff_date;

        SET @batch_deleted = @@ROWCOUNT;
        SET @total_deleted = @total_deleted + @batch_deleted;

        -- Brief pause to let other queries run
        IF @batch_deleted > 0
            WAITFOR DELAY '00:00:01';
    END;
END;

Batch deletion prevents the database from acquiring a table-level lock, which would block all other operations on the table during a large delete.

Stored Procedures vs Functions vs Views

These three database objects serve different purposes. Here is when to use each one.

Feature	Stored Procedure	Function	View
Can modify data	Yes	No (usually)	No
Returns a value	Via OUT params	Yes (required)	Result set
Usable in SELECT	No	Yes	Yes
Transaction control	Yes	No	No
Parameters	IN, OUT, INOUT	Input only	None
Best for	Multi-step operations	Calculations, transforms	Simplifying queries

Use a stored procedure when you need to perform multiple operations (inserts, updates, deletes) in a single transaction. Use a function when you need a reusable calculation inside a SELECT statement. Use a view when you want to simplify a complex query that gets reused as a virtual table.

Best Practices and Performance Tips

Following these guidelines will keep your stored procedures maintainable, performant, and secure.

Naming Conventions

• Use a consistent prefix like sp_ or usp_ for user stored procedures. Avoid sp_ in SQL Server because Microsoft reserves it for system procedures, which can cause a performance hit as the engine checks the master database first.
• Name procedures by action: RegisterUser, GenerateReport, CleanupExpiredSessions. The name should tell you what the procedure does without reading the body.

Keep Procedures Focused

• Each procedure should do one thing well. If you find a procedure growing past 100 lines, consider splitting it into smaller procedures that call each other.
• Avoid creating "god procedures" that accept a mode parameter and do completely different things based on its value.

Always Use Transactions

• Wrap multi-statement operations in explicit transactions. If the third INSERT in a five-step procedure fails, you want all previous changes rolled back.
• Keep transactions as short as possible. Long-running transactions hold locks and block other users.

Avoid SELECT * Inside Procedures

• Specify column names explicitly. If someone adds a column to the table later, SELECT * changes the procedure's output without warning, which can break applications that depend on a specific column order.

Parameterize Everything

• Never build SQL strings by concatenating parameter values. Use the parameter directly in your query. Dynamic SQL inside a procedure is acceptable when necessary (e.g., dynamic table names), but always use sp_executesql with parameters in SQL Server or EXECUTE ... USING in PostgreSQL.

Comment Complex Logic

• SQL does not have the same tooling as application code. A brief comment explaining why a particular check exists saves the next developer from guessing.

Performance Tips

• Use SET NOCOUNT ON in SQL Server. It eliminates the "N rows affected" messages for every statement, reducing network overhead.
• Avoid cursors when possible. Set-based operations are almost always faster than row-by-row cursor processing. If you need to process rows individually, consider a WHILE loop with a TOP 1 pattern instead.
• Watch for parameter sniffing in SQL Server. The cached execution plan is optimized for the first parameter values used. If later calls use very different values, performance can degrade. Use OPTION (RECOMPILE) for procedures where this is a problem.
• Index the columns referenced in your procedure's WHERE clauses and JOIN conditions. A procedure is only as fast as the queries inside it.

Skip writing procedures from scratch - describe what you need in plain English and AI2SQL generates the SQL for you, including stored procedure syntax for your specific database.

Frequently Asked Questions

What is the difference between a stored procedure and a function in SQL?

Stored procedures can perform actions like INSERT, UPDATE, DELETE, and transaction control. They do not return a value directly (though they can use OUT parameters). Functions must return a value and are designed to be used inside SQL expressions like SELECT statements. Functions generally cannot modify data, while stored procedures can.

Can stored procedures improve query performance?

Yes. Stored procedures are parsed, compiled, and cached on the server after their first execution. Subsequent calls reuse the cached execution plan, which eliminates repeated parsing and optimization overhead. They also reduce network traffic by executing multiple SQL statements in a single call instead of sending each statement individually from the application.

Are stored procedures safe from SQL injection?

Stored procedures that use parameterized inputs are resistant to SQL injection because the database treats parameter values as data, not executable code. However, if a stored procedure builds dynamic SQL by concatenating user input into a query string and then executes it, it remains vulnerable. Always use parameterized queries inside your procedures.

How do I debug a stored procedure?

Debugging approaches vary by database. SQL Server Management Studio has a built-in step debugger. In PostgreSQL, you can use RAISE NOTICE to print variable values during execution. In MySQL, you can use SELECT statements as breakpoints to output intermediate values. Logging intermediate results into a debug table is a cross-platform technique that works everywhere.

Should I use stored procedures or write SQL in my application code?

It depends on your use case. Stored procedures are ideal for complex business logic that involves multiple SQL statements, transaction control, and data validation close to the database. Application-side SQL (especially with an ORM or query builder) is better when you need portability across databases, easier version control, and tighter integration with application logic. Many teams use a mix of both.