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:
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:
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:
Starting with simple SELECT or INSERT stored procedures and progressing toward transactional, validated, and error-handled procedures makes SQL development structured and scalable.