Beginner SQL Basics: Part 2 – Stored Procedures and Functions

Welcome back to our Beginner SQL series! In Part 1 , we covered the fundamentals of SQL, including SELECT queries, filtering with WHERE, joins, and modifying data using INSERT, UPDATE, and DELETE statements. Now it’s time to level up your SQL skills by learning about Stored Procedures and Functions.

Think of stored procedures and functions as reusable recipes inside your database. Instead of rewriting the same SQL logic repeatedly, you store it once and execute it whenever needed. This improves performance, reduces errors, and keeps your database logic consistent across applications.

What Are Stored Procedures?

A stored procedure is a precompiled collection of SQL statements stored in the database. It can accept parameters, execute business logic, and return results.

Creating Your First Stored Procedure

The following example creates a stored procedure that inserts a product only if it does not already exist.

Executing a Stored Procedure

Once the procedure is created, you can execute it using EXEC in SQL Server or CALL in PostgreSQL.

What Are Functions?

Functions are reusable database objects designed to return a value. They are commonly used inside SELECT statements, WHERE clauses, and JOIN conditions.

Creating Scalar Functions

Scalar functions return a single value, such as a calculated discount or formatted string.

Creating Table-Valued Functions

Table-valued functions return an entire result set and behave similarly to parameterized views.

Stored Procedures vs Functions

Stored procedures are ideal for actions like INSERT, UPDATE, and DELETE, while functions are best for reusable calculations.

Parameters and Output Parameters

Stored procedures support output parameters, allowing them to return additional values such as newly created IDs.

Error Handling and Transactions

Transactions and TRY/CATCH blocks help ensure data consistency during critical operations like transferring money between accounts.

Best Practices

Always use meaningful naming conventions, test procedures in development environments, and document complex logic.

SQL Code Examples

Stored Procedure:
--------------------------------------------------------------------

<code>
CREATE PROCEDURE AddProduct
    @ProductName NVARCHAR(100),
    @Price DECIMAL(10,2),
    @CategoryID INT
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductName = @ProductName)
    BEGIN
        INSERT INTO Products (ProductName, Price, CategoryID)
        VALUES (@ProductName, @Price, @CategoryID);

        PRINT 'Product added successfully.';
    END
    ELSE
    BEGIN
        PRINT 'A product with this name already exists.';
    END
END;
</code>

<code>
EXEC AddProduct 'Gaming Mouse', 59.99, 3;
</code>

Function:
-------------------------------------------------------------------------------------

<code>
CREATE FUNCTION GetDiscountedPrice
(
    @OriginalPrice DECIMAL(10,2),
    @LoyaltyTier CHAR(1)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Discount DECIMAL(3,2);

    IF @LoyaltyTier = 'G' SET @Discount = 0.10;
    ELSE IF @LoyaltyTier = 'S' SET @Discount = 0.05;
    ELSE SET @Discount = 0.00;

    RETURN @OriginalPrice * (1 - @Discount);
END;
</code>

<code>
SELECT
    ProductName,
    Price AS OriginalPrice,
    dbo.GetDiscountedPrice(Price, 'G') AS DiscountedPrice
FROM Products;
</code>

<code>
CREATE FUNCTION GetCustomersByCity(@City NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT CustomerID, CustomerName, Email
    FROM Customers
    WHERE City = @City
);
</code>

<code>
SELECT * FROM dbo.GetCustomersByCity('London');
</code>

Handling Exception in Stored Procedure Block:
----------------------------------------------------------------------

<code>
CREATE PROCEDURE TransferFunds
    @FromAccountID INT,
    @ToAccountID INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Accounts
        SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccountID
          AND Balance >= @Amount;

        IF @@ROWCOUNT = 0
            THROW 50000, 'Insufficient funds or invalid account.', 1;

        UPDATE Accounts
        SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccountID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Transfer failed: ' + ERROR_MESSAGE();
    END CATCH;
END;
</code>

Conclusion

Stored procedures and functions are essential concepts in database programming. They help you build reusable, maintainable, and efficient SQL applications. By mastering these concepts, you’ll be able to centralize business logic, improve security, and create more scalable systems.

In the next part of this series, you’ll learn about SQL Triggers and Views.