The Code Neuron

Roadmaps
Tech Stack
Get In Touch

Stored Procedures and Rules: Enhancing Database Control

—

by

thecodeneuron
in ,

Welcome back to our MSSQL journey! In the previous post, we explored the power of SQL Triggers for automating database actions. Now, let’s unlock two more powerful features that give you greater control over your database operations and data integrity: Stored Procedures and Rules.

These tools allow for the encapsulation of complex logic, enhance security, and improve performance. For freshers, think of them as pre-written, reusable scripts that you can call whenever needed, simplifying your database interactions. As always, we’ll continue to use our familiar eCommerce database with TeamMembers, ClientAccounts, and Transactions tables to keep our examples consistent! So, before you begin and if you haven’t ready with these databases, go back on our last blog and help yourself to create/populate the tables mentioned.

Understanding Stored Procedures: Your Reusable Command Center

A Stored Procedure is a prepared SQL code that you can save, so the code can be reused over and over again. If you have a SQL query that you write frequently, save it as a stored procedure, and then just call it to execute. This not only saves time but also offers several benefits:

  • Reusability: Write once, use many times.
  • Performance: Pre-compiled execution plans often lead to faster execution.
  • Security: Users can be granted permission to execute a procedure without being granted direct access to the underlying tables.
  • Data Integrity: Centralize business logic to ensure consistent application.
  • Reduced Network Traffic: Instead of sending multiple SQL statements, only the procedure name and parameters are sent.

Syntax for Creating a Stored Procedure

SQL
CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype
AS
BEGIN
    -- SQL statements to be executed
END;
GO

Examples of Stored Procedures

Let’s look at examples using our eCommerce database tables: TeamMembers, ClientAccounts, and Transactions.

1. Procedure to Add a New Transaction

Instead of writing a full INSERT statement every time, you can create a procedure to simplify adding new transactions, linking them to existing clients:

SQL
-- 1. Procedure to Add a New Transaction
-- This procedure simplifies inserting new transactions, with a check for client_id existence.
CREATE PROCEDURE AddNewTransaction
    @transaction_id INT,
    @amount DECIMAL(7,2),
    @transaction_date DATE,
    @client_id INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents messages showing the number of rows affected

    -- Validate if the client_id exists in the ClientAccounts table
    IF NOT EXISTS (SELECT 1 FROM ClientAccounts WHERE client_id = @client_id)
    BEGIN
        PRINT 'Error: Client ID does not exist.';
        RETURN; -- Exit the procedure if client ID is invalid
    END

    -- Insert the new transaction into the Transactions table
    INSERT INTO Transactions (transaction_id, amt, odate, cnum)
    VALUES (@transaction_id, @amount, @transaction_date, @client_id);

    -- Note: If you have a trigger (e.g., trg_UpdateClientTotalOnInsert_Join from previous blog)
    -- it would automatically update related client data upon this insert.
END;
GO

Once executed, you can check your Stored Procedure –

How it works: This procedure takes transaction details and a client_id. It first validates if the client_id exists in ClientAccounts. If valid, it then inserts the new transaction into the Transactions table. (Remember, if you have a trigger set up from our last blog, it would automatically update the client’s total transaction amount!)

Execution:

  • For Valid Client
SQL
--Example for an valid client:
EXEC AddNewTransaction 3017, 2500.00, '2025-07-23', 2001;
--Check the new transaction in Transactions table
SELECT * FROM Transactions;
--Also check the Total Amount updated due to trigger
SELECT * FROM ClientAccounts;
  • Output: You can see the new transaction in the Transactions table, as well as, ClientAccounts table also updated with the correct Total.
  • For Invalid Client
SQL
-- Example for an invalid client:
EXEC AddNewTransaction 3018, 500.00, '2025-07-23', 9999;
  • Output:

2. Procedure to Retrieve Transactions for a Specific Client

To quickly see all transactions for a particular client:

SQL
-- 2. Procedure to Retrieve Transactions for a Specific Client
-- This procedure fetches all transaction details for a given client ID.
CREATE PROCEDURE GetClientTransactions
    @client_id INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents messages showing the number of rows affected

    -- Select transaction details and the associated client name
    SELECT
        t.transaction_id,
        t.amt AS transaction_amount,
        t.odate AS transaction_date,
        ca.client_name
    FROM
        Transactions t
    INNER JOIN
        ClientAccounts ca ON t.cnum = ca.client_id
    WHERE
        ca.client_id = @client_id
    ORDER BY
        t.odate DESC; -- Order results by transaction date in descending order
END;
GO

How it works: This procedure takes a client_id as input and returns all transaction details for that client, including their name for clarity.

Execution:

SQL
-- Execution of GetClientTransactions
EXEC GetClientTransactions 2001;
  • Output:

3. Procedure to Calculate Total Commission for All Team Members

This procedure calculates the total commission earned by each team member based on the transactions of clients they are assigned to.

SQL
-- 3. Procedure to Calculate Total Commission for All Team Members
-- This procedure calculates the total commission for each team member based on client transactions.
CREATE PROCEDURE CalculateTeamMemberCommissions
AS
BEGIN
    SET NOCOUNT ON; -- Prevents messages showing the number of rows affected

    -- Join tables to calculate commission (transaction amount * commission rate)
    -- LEFT JOIN is used to include all team members, even those without transactions (commission will be 0)
    SELECT
        tm.member_name,
        tm.commission_rate,
        ISNULL(SUM(t.amt * tm.commission_rate), 0) AS total_commission
    FROM
        TeamMembers tm
    LEFT JOIN
        ClientAccounts ca ON tm.member_id = ca.assigned_member_id
    LEFT JOIN
        Transactions t ON ca.client_id = t.cnum
    GROUP BY
        tm.member_name, tm.commission_rate -- Group by member to aggregate commissions
    ORDER BY
        total_commission DESC; -- Order results by total commission in descending order
END;
GO

How it works: This procedure joins TeamMembers, ClientAccounts, and Transactions tables. It calculates commission by multiplying each transaction amount by the assigned team member’s commission rate. It uses LEFT JOIN to include team members even if they haven’t had any transactions yet, showing their commission as 0 using ISNULL.

Execution:

SQL
-- Execution of CalculateTeamMemberCommissions
EXEC CalculateTeamMemberCommissions;
  • Output:

4. Procedure to Update a Client’s Satisfaction Rating

This procedure allows for easy updates to a client’s satisfaction rating.

SQL
-- 4. Procedure to Update a Client’s Satisfaction Rating
-- This procedure updates the satisfaction rating for a specific client, including basic validation.
CREATE PROCEDURE UpdateClientSatisfaction
    @client_id INT,
    @new_rating INT
AS
BEGIN
    SET NOCOUNT ON; -- Prevents messages showing the number of rows affected

    -- Validate the new_rating to be within a reasonable range (e.g., 100 to 500)
    IF @new_rating < 100 OR @new_rating > 500
    BEGIN
        PRINT 'Error: Satisfaction rating must be between 100 and 500.';
        RETURN; -- Exit the procedure if rating is out of bounds
    END

    -- Update the satisfaction rating for the specified client
    UPDATE ClientAccounts
    SET satisfaction_rating = @new_rating
    WHERE client_id = @client_id;

    -- Check if any row was actually updated
    IF @@ROWCOUNT = 0
    BEGIN
        PRINT 'Warning: No client found with the provided ID, or rating was already the same.';
    END
END;
GO

How it works: This procedure takes a client_id and a new_rating. It includes basic validation for the rating range and updates the satisfaction_rating in the ClientAccounts table. It also provides feedback if no client was found.

Execution:

SQL
EXEC UpdateClientSatisfaction 2001, 250;
-- Try with an invalid rating:
-- EXEC UpdateClientSatisfaction 2002, 50;
  • Output:

System Stored Procedures: Your Database’s Built-in Helpers

Beyond the custom stored procedures you create, SQL Server comes packed with a powerful set of System Stored Procedures. These are pre-defined procedures that administrators and developers use to perform a wide range of tasks, from getting information about database objects to managing server configurations. They reside primarily in the master database but can be executed from any database.

Think of them as built-in commands that help you understand and control your SQL Server environment without writing complex queries from scratch.

Examples of Common System Stored Procedures

1. sp_help

This procedure provides information about a database object (table, view, stored procedure, etc.) or a data type. It’s incredibly useful for quick inspection.

Syntax:

SQL
EXEC sp_help [object_name];

Example: To get details about your ClientAccounts table:

SQL
EXEC sp_help 'ClientAccounts';
GO

2. sp_who / sp_who2

These procedures provide information about current users, sessions, and processes in the SQL Server instance. sp_who2 offers more detailed information than sp_who. This is invaluable for troubleshooting performance or connection issues.

Syntax:

SQL
EXEC sp_who;
-- OR
EXEC sp_who2;

Example: To see who’s connected to your database right now:

SQL
EXEC sp_who;
GO

3. sp_spaceused

This procedure displays the number of rows, disk space reserved, and disk space used by a table or an indexed view, or by the whole database.

Syntax:

SQL
EXEC sp_spaceused [object_name];

Example: To check the space used by your Transactions table:

SQL
EXEC sp_spaceused 'Transactions';
GO

Understanding Rules: Defining Data Constraints (Legacy Feature)

Rules were a feature in older versions of SQL Server used to define constraints for columns, ensuring data consistency. A rule binds a specific condition to a column, and any attempt to insert or update data that violates this condition is prevented.

Important Note: While rules exist, in modern SQL Server and best practices, CHECK Constraints are the preferred and more powerful way to enforce data validation. Rules are considered a legacy feature and may be deprecated in future versions. You’ll almost always use CHECK constraints in real-world scenarios for better flexibility and integration with table definitions.

Syntax for Creating a Rule

SQL
CREATE RULE rule_name
AS @column_value_variable condition;
GO

Examples of Rules

Let’s explore the concept with examples from our eCommerce database, keeping in mind the deprecation.

1. Rule for Allowed Client Cities

This rule restricts the client_city column in ClientAccounts to only allow specific values:

SQL
-- 1. Rule for Allowed Client Cities
-- This rule restricts client_city to specific values.
-- Note: This uses cities from the pre-populated data for consistency.
CREATE RULE AllowedClientCities
AS @city IN ('New York', 'Los Angeles', 'Chicago', 'Houston');
GO

Check the Rules to view newly created Rule

Binding the Rule: After creating, you need to bind it to a column.

SQL
-- Binding the rule to the client_city column in ClientAccounts table
EXEC sp_bindrule 'dbo.AllowedClientCities', 'ClientAccounts.client_city';

How it works: Any INSERT or UPDATE operation on the client_city column in the ClientAccounts table will fail if the city is not ‘London’, ‘San Jose’, ‘Rome’, or ‘Berlin’.

Example of a failed insertion:

SQL
INSERT INTO ClientAccounts VALUES (2009, 'New Corp', 'Paris', 150, 101);
-- This will result in an error: "A column insert or update conflicts with a rule..."
  • Output:

2. Rule for Positive Transaction Amounts

This rule ensures that the amt column from the Transactions table only accepts positive values (greater than zero).

SQL
-- 2. Rule for Positive Transaction Amounts
-- This rule ensures transaction amounts are greater than zero.
CREATE RULE PositiveTransactionAmount
AS @amt > 0;
GO

Binding the Rule:

SQL
-- Binding the rule to the amt column in Transactions table
EXEC sp_bindrule 'dbo.PositiveTransactionAmount', 'Transactions.amt';

How it works: Any INSERT or UPDATE on the amt column in the Transactions table will be rejected if the value is less than or equal to zero.

Example of a failed insertion:

SQL
INSERT INTO Transactions VALUES (3018, -100.00, '2025-07-23', 2001);
-- This will result in an error.

Unbinding and Dropping Rules

You cannot directly delete a rule if it’s bound to a column. First, you must unbind it:

SQL
-- Unbinding and Dropping Rules (Important: Must unbind before dropping)
EXEC sp_unbindrule 'ClientAccounts.client_city';
EXEC sp_unbindrule 'Transactions.amt';

After unbinding, you can drop the rule using:

SQL
DROP RULE rule_name;

Understanding CHECK Constraints: The Modern Way to Validate Data

As mentioned, Rules are a legacy feature. The modern and recommended approach for enforcing domain integrity (i.e., restricting the values that can be entered into a column) is using CHECK Constraints.

CHECK constraints are more flexible and are considered part of the table’s definition, making database schema management more intuitive. They allow you to define a condition that must be true for every row in the table. If an INSERT or UPDATE statement causes the condition to evaluate to false, the statement will fail.

Syntax for Creating a CHECK Constraint

You can add CHECK constraints at the time of table creation or alter an existing table.

1. Inline (Column-level) CHECK Constraint: Applies a constraint to a single column.

SQL
CREATE TABLE TableName (
    ColumnName DataType CHECK (condition)
);

2. Table-level CHECK Constraint: Applies a constraint that can involve multiple columns in the same table.

SQL
CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    CONSTRAINT ConstraintName CHECK (condition)
);

3. Adding to an Existing Table:

SQL
ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (condition);

Examples of CHECK Constraints

Let’s re-implement the logic from our “Rules” examples using CHECK constraints, which is the best practice.

1. CHECK Constraint for Allowed Client Cities

This constraint ensures that the client_city column in ClientAccounts only allows specific values, just like our AllowedClientCities rule.

SQL
-- 1. CHECK Constraint for Allowed Client Cities
-- This constraint ensures client_city is one of the allowed values.
ALTER TABLE ClientAccounts
ADD CONSTRAINT CHK_ClientCity_Allowed
CHECK (client_city IN ('London', 'San Jose', 'Rome', 'Berlin'));
GO

How it works: Now, any attempt to INSERT or UPDATE a client_city in ClientAccounts that is not one of the specified cities will be rejected.

Example of a failed insertion:

SQL
INSERT INTO ClientAccounts (client_id, client_name, client_city, satisfaction_rating, assigned_member_id)
VALUES (2009, 'New Corp', 'Paris', 150, 101);
-- This will result in an error: "The INSERT statement conflicted with the CHECK constraint..."

Dropping CHECK Constraints

To remove a CHECK constraint, you use the ALTER TABLE DROP CONSTRAINT statement, referencing the constraint’s name.

SQL
-- Dropping CHECK Constraints
ALTER TABLE ClientAccounts
DROP CONSTRAINT CHK_ClientCity_Allowed;

Conclusion

Stored Procedures are indispensable tools for building efficient, secure, and maintainable database applications by encapsulating and reusing SQL logic. They are a cornerstone of professional SQL development, both custom ones you write and the built-in System Stored Procedures that aid in database administration.

While Rules served a similar purpose in the past for enforcing data constraints, modern SQL development strongly favors CHECK Constraints for data validation. Always opt for CHECK constraints over rules in new development.

Mastering Stored Procedures is a significant step in becoming proficient in SQL. Our next topic will delve into User-Defined Functions, another powerful way to encapsulate and reuse logic in your SQL Server database.

Stay tuned for more!

Hi there 👋
It’s nice to see you.

Sign up to receive awesome content in your inbox, as soon as they gets posted!

This field is required.

We don’t spam! Read our privacy policy for more info.

Comments

Leave a Reply