How to Create a Stored Procedure in SQL

Use a stored procedure when database logic should be saved, reusable, parameterized, and callable on demand. This is ideal for admin actions, ETL jobs, controlled updates, and repeatable business workflows.

What you’ll build or solve

You’ll learn how to create a stored procedure in SQL and call it with parameters. You’ll also know when a procedure is better than repeating multi-step SQL manually.

When this approach works best

This approach is the right choice when logic needs reuse, permissions control, or multi-step transactional execution.

Common real-world scenarios include:

• Monthly billing jobs
• User cleanup tasks
• Controlled account updates
• ETL transformations
• Audit-safe admin actions

This is a bad idea when a simple reusable SELECT view already solves the problem.

Prerequisites

You only need:

• Database support for stored procedures
• Permission to create programmable objects
• Basic SQL control-flow knowledge

Step-by-step instructions

Step 1: Create the procedure with parameters

The syntax differs by database. Here is a simple SQL Server style example.

CREATE PROCEDURE GetPaidOrders
  @MinTotal DECIMAL(10,2)
AS
BEGIN
  SELECT *
  FROM orders
  WHERE status = 'paid'
    AND total >= @MinTotal;
END;

Run it like this:

EXEC GetPaidOrders @MinTotal = 100;

This keeps reusable business logic close to the data.

What to look for:

• Procedures save executable logic
• Parameters make them reusable
• Great for admin workflows
• Can contain multiple SQL statements
• Syntax differs by database

Examples you can copy

Cleanup inactive users

CREATE PROCEDURE DeleteInactiveUsers
AS
BEGIN
  DELETE FROM users
  WHERE last_login < '2025-01-01';
END;

Revenue threshold report

CREATE PROCEDURE HighRevenueCountries
  @MinRevenue DECIMAL(10,2)

Product price update

CREATE PROCEDURE UpdatePrice
  @ProductId INT,
  @NewPrice DECIMAL(10,2)

Common mistakes and how to fix them

Mistake 1: Using procedures for simple reusable reads

What the reader might do:

Create a procedure for a single static report query.

Why it breaks: a view may be simpler and more transparent.

Corrected approach:

Use procedures for parameterized workflows or multi-step logic.

Mistake 2: Hardcoding values instead of parameters

What the reader might do:

WHERE total > 100

Why it breaks: reuse becomes limited.

Corrected approach:

Expose the threshold as a parameter.

Mistake 3: Forgetting transaction safety in multi-step updates

What the reader might do:

Run multiple writes without transaction control.

Why it breaks: partial failures can corrupt workflow state.

Corrected approach:

Wrap multi-step procedures in transactions.

Troubleshooting

If the syntax errors, confirm your database-specific procedure syntax.

If logic should just expose reusable rows, consider a view.

If parameters do not work, verify placeholder syntax for your engine.

If multi-step writes fail halfway, add transactions.

Quick recap

• Stored procedures save executable SQL workflows
• Parameters make them reusable
• Great for admin and ETL logic
• Better than repeating multi-step SQL
• Use transactions for multi-write safety