Introduction

When applications grow, repeating SQL queries across code becomes risky and inefficient. Every database-driven system needs a way to centralize SQL logic, enforce validation, improve performance, and reduce duplication. This is where Stored Procedures become essential.

A Stored Procedure (often called SP or Proc) is a reusable SQL program stored in the database. Instead of sending raw SQL every time, applications call the stored procedure with input values.

Stored procedures are heavily used in:

This article explains how stored procedures work, why they matter, how to write them correctly, and how to safely use them at beginner and intermediate levels.

Real-World Context

We continue using the same automotive service dataset from previous articles.

Tables:

We now want to:

These are perfect use cases for stored procedures.

What Is a Stored Procedure?

A stored procedure is a precompiled SQL script stored in the database. It can contain:

Example of calling a stored procedure

EXEC GetCustomerById @CustomerId = 101;

Why Use Stored Procedures?

BenefitExplanation
PerformanceSQL is compiled once and reused, improving execution time
SecurityPrevents SQL injection by parameterizing queries
ReusabilityLogic is written once and used by multiple apps
MaintainabilityChanging logic in DB means no code deployments
Transaction SafetySupports rollback and ACID consistency

Types of Stored Procedures

TypePurpose
CRUD ProceduresBasic Insert/Update/Delete
Reporting ProceduresAggregations, grouping, analytics
Utility ProceduresSystem checks, background tasks

In enterprise architecture, stored procedures often follow naming patterns such as:

Creating Your First Stored Procedure

Example: Fetch customer by ID.

CREATE PROCEDURE GetCustomerById
    @CustomerId INTASBEGIN
    SELECT CustomerId, FullName, City
    FROM Customer
    WHERE CustomerId = @CustomerId;
END;

Execute it:

EXEC GetCustomerById @CustomerId = 2;

Stored Procedure with Input Parameters

Example: Fetch service records filtered by date range.

CREATE PROCEDURE GetServiceRecords
    @CarId INT,
    @StartDate DATE,
    @EndDate DATEASBEGIN
    SELECT ServiceId, ServiceDate, Cost, Description
    FROM ServiceRecord
    WHERE CarId = @CarId
      AND ServiceDate BETWEEN @StartDate AND @EndDate
    ORDER BY ServiceDate DESC;
END;

Stored Procedure for Insert Operations

Example: Insert a new service record.

CREATE PROCEDURE AddServiceRecord
    @CarId INT,
    @ServiceDate DATE,
    @Cost DECIMAL(10,2),
    @Description NVARCHAR(500)
ASBEGIN
    INSERT INTO ServiceRecord (CarId, ServiceDate, Cost, Description)
    VALUES (@CarId, @ServiceDate, @Cost, @Description);

    SELECT SCOPE_IDENTITY() AS NewServiceId;
END;

Why return SCOPE_IDENTITY()?

So the application receives the newly created record ID.

Stored Procedure with Update Logic

CREATE PROCEDURE UpdateServiceRecord
    @ServiceId INT,
    @Cost DECIMAL(10,2),
    @Description NVARCHAR(500)
ASBEGIN
    UPDATE ServiceRecord
    SET Cost = @Cost,
        Description = @Description
    WHERE ServiceId = @ServiceId;

    SELECT @@ROWCOUNT AS RecordsUpdated;
END;

Stored Procedure with DELETE and Safety Check

Deleting records can be risky. Always confirm existence or add a soft-delete pattern.

CREATE PROCEDURE DeleteServiceRecord
    @ServiceId INTASBEGIN
    IF EXISTS (SELECT 1 FROM ServiceRecord WHERE ServiceId = @ServiceId)
    BEGIN
        DELETE FROM ServiceRecord WHERE ServiceId = @ServiceId;
        SELECT 'Record Deleted' AS Message;
    END
    ELSE
    BEGIN
        SELECT 'Record Not Found' AS Message;
    ENDEND;

Adding Error Handling with TRY...CATCH

Intermediate-level stored procedures must include error handling.

CREATE PROCEDURE SafeAddServiceRecord
    @CarId INT,
    @Cost DECIMAL(10,2),
    @ServiceDate DATEASBEGIN
    BEGIN TRY
        INSERT INTO ServiceRecord(CarId, ServiceDate, Cost)
        VALUES (@CarId, @ServiceDate, @Cost);

        SELECT 'Success' AS Result;
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

Transaction-Based Stored Procedure

For multi-step operations, use transactions.

CREATE PROCEDURE AddCarAndService
    @CustomerId INT,
    @Brand NVARCHAR(50),
    @Model NVARCHAR(50),
    @Cost DECIMAL(10,2)
ASBEGIN
    BEGIN TRANSACTION;

    BEGIN TRY
        INSERT INTO Car (CustomerId, Brand, Model)
        VALUES (@CustomerId, @Brand, @Model);

        DECLARE @CarId INT = SCOPE_IDENTITY();

        INSERT INTO ServiceRecord (CarId, ServiceDate, Cost)
        VALUES (@CarId, GETDATE(), @Cost);

        COMMIT TRANSACTION;
        SELECT 'Success' AS Result;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

How Applications Call Stored Procedures

C#

var cmd = new SqlCommand("GetServiceRecords", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CarId", 10);
cmd.Parameters.AddWithValue("@StartDate", "2024-01-01");
cmd.Parameters.AddWithValue("@EndDate", "2024-12-31");

Angular or REST Consumer (via API)

Stored procedures execute via backend API, not frontend.

Best Practices

RecommendationReason
Use meaningful namesEasier maintenance
Avoid SELECT *Prevents unnecessary column loading
Return error messagesBetter debugging
Use transactions in multi-step operationsPrevent partial failures
Never include business logic in 10-line nested queriesSplit into readable statements

Common Mistakes and Fixes

MistakeFix
No parameters, concatenating SQL stringsAlways use parameters
Not returning key valuesUse SCOPE_IDENTITY()
Update affecting multiple rows accidentallyAlways validate input

Summary

Stored procedures are a powerful tool in SQL Server that:

Starting with simple SELECT or INSERT stored procedures and progressing toward transactional, validated, and error-handled procedures makes SQL development structured and scalable.