SQL Programming

This section covers advanced SQL programming concepts, including stored procedures, user-defined functions, triggers, cursors, and transaction management. Mastering these elements allows for more complex, efficient, and robust database interactions.

Stored Procedures

Stored procedures are precompiled SQL statements or a set of SQL statements that are stored in the database. They offer benefits such as improved performance, enhanced security, and code reusability.

Creating a Stored Procedure

The basic syntax for creating a stored procedure involves the CREATE PROCEDURE statement.

CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    -- Select all orders for the given CustomerID
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
            

Executing a Stored Procedure

You can execute a stored procedure using the EXEC or EXECUTE command.

EXEC GetCustomerOrders 101;
            

User-Defined Functions (UDFs)

UDFs are routines that accept parameters, perform actions (like computations or data manipulations), and return a value. They can be scalar (returning a single value) or table-valued (returning a table).

Scalar UDF Example

CREATE FUNCTION CalculateTotalOrderValue (@OrderID INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Total DECIMAL(10,2);
    SELECT @Total = SUM(Quantity * Price)
    FROM OrderDetails
    WHERE OrderID = @OrderID;
    RETURN @Total;
END;
            

Using a Scalar UDF

SELECT OrderID, dbo.CalculateTotalOrderValue(OrderID) AS OrderTotal
FROM Orders;
            

Triggers

Triggers are a special type of stored procedure that automatically executes or fires in response to certain events on a particular table or view in a database. Common events include INSERT, UPDATE, and DELETE operations.

Creating a Trigger

CREATE TRIGGER UpdateProductInventory
ON OrderDetails
AFTER INSERT
AS
BEGIN
    -- Update the product's stock count after an order item is inserted
    UPDATE Products
    SET StockQuantity = Products.StockQuantity - i.Quantity
    FROM Products
    INNER JOIN inserted i ON Products.ProductID = i.ProductID;
END;
            

Cursors

Cursors allow you to process rows in a result set one by one. While powerful for row-by-row operations, they are generally less performant than set-based operations and should be used only when necessary.

Cursor Example

DECLARE @ProductName VARCHAR(50);
DECLARE product_cursor CURSOR FOR
SELECT ProductName FROM Products;

OPEN product_cursor;
FETCH NEXT FROM product_cursor INTO @ProductName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process @ProductName (e.g., print it)
    PRINT @ProductName;

    FETCH NEXT FROM product_cursor INTO @ProductName;
END;

CLOSE product_cursor;
DEALLOCATE product_cursor;
            

Transactions

Transactions are a sequence of operations performed as a single logical unit of work. They ensure data integrity by adhering to ACID properties (Atomicity, Consistency, Isolation, Durability).

Transaction Control Statements

• BEGIN TRANSACTION (or BEGIN TRAN): Starts a transaction.
• COMMIT TRANSACTION (or COMMIT TRAN): Saves all changes made during the transaction.
• ROLLBACK TRANSACTION (or ROLLBACK TRAN): Undoes all changes made during the transaction.

Transaction Example

BEGIN TRANSACTION;

TRY
BEGIN
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

    -- Simulate a potential error
    IF 1 = 2 BEGIN
        RAISERROR('Simulated error occurred.', 16, 1);
    END

    -- If all operations are successful, commit the transaction
    COMMIT TRANSACTION;
    PRINT 'Transaction committed successfully.';
END
END

CATCH
BEGIN
    -- If an error occurred, rollback the transaction
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    PRINT 'Transaction rolled back.';
    -- Handle the error further if needed
    THROW;
END
END