How to Create Stored Procedures in SQL Server | Beginner to Intermediate Practical Guide

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:

Enterprise ERP systems
Banking applications
E-commerce platforms
Multi-tenant SaaS products
API-driven backend architecture

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:

Customer
Car
ServiceRecord

We now want to:

Insert a new service record
Update an existing record
Fetch service history for a car

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:

SELECT
INSERT
UPDATE
DELETE
Variables
Conditions
Transactions
Loops (rarely needed)

Example of calling a stored procedure

EXEC GetCustomerById @CustomerId = 101;

Why Use Stored Procedures?

Benefit	Explanation
Performance	SQL is compiled once and reused, improving execution time
Security	Prevents SQL injection by parameterizing queries
Reusability	Logic is written once and used by multiple apps
Maintainability	Changing logic in DB means no code deployments
Transaction Safety	Supports rollback and ACID consistency

Types of Stored Procedures

Type	Purpose
CRUD Procedures	Basic Insert/Update/Delete
Reporting Procedures	Aggregations, grouping, analytics
Utility Procedures	System checks, background tasks

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

uspCustomerInsert
uspGetServiceRecordByCarId
uspUpdateServiceCost

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

Recommendation	Reason
Use meaningful names	Easier maintenance
Avoid SELECT *	Prevents unnecessary column loading
Return error messages	Better debugging
Use transactions in multi-step operations	Prevent partial failures
Never include business logic in 10-line nested queries	Split into readable statements

Common Mistakes and Fixes

Mistake	Fix
No parameters, concatenating SQL strings	Always use parameters
Not returning key values	Use SCOPE_IDENTITY()
Update affecting multiple rows accidentally	Always validate input

Summary

Stored procedures are a powerful tool in SQL Server that:

Improve performance
Increase security
Centralize logic
Reduce risk of SQL injection
Support transactions and error handling

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