Stored Procedure

Stored procedures are precompiled sets of one or more SQL statements that can be executed together. They are stored in the database and can be accessed by applications, other stored procedures, or scripts. These procedures encapsulate intricate SQL logic, making maintenance and reusability easier.

Characteristics

1. Precompiled: Stored procedures are compiled and saved in the database, potentially improving performance.
2. Reusable: Once created, they can be utilized multiple times by various applications.
3. Modular: They enable you to consolidate complex logic into a single callable unit.
4. Secure: Data access can be finely controlled by granting permissions on stored procedures rather than tables.
5. Maintainable: Modifications to the procedure logic only need to be done in one location.

Advantages of Stored Procedures

1. Performance
   • Stored procedures are compiled and optimized by the database server before execution, reducing runtime overhead.
   • Sending a single call to a stored procedure instead of multiple SQL queries decreases network traffic.
2. Security
   • Permissions can be granted on stored procedures, adding an extra layer of security.
   • Using parameters in stored procedures helps prevent SQL injection attacks.
3. Maintainability and Reusability
   • Complex business logic can be centralized within stored procedures, making maintenance easier.
   • Procedures can be reused across multiple applications, reducing redundancy and ensuring consistency.
4. Modularity
   • Complex operations can be encapsulated in stored procedures, making tasks more manageable.
5. Transaction Management
   • Stored procedures can manage transactions using BEGIN TRANSACTION, COMMIT, and ROLLBACK to ensure the successful completion of operations.

Flow of procedure

Description of the above flow

• Client Application: The client application triggers the stored procedure.
• Stored Procedure Logic: The stored procedure carries out its business logic, such as data modifications, calculations, etc.
• Result Set: A result set is produced by the stored procedure according to its operations.
• Retrieve Result: The client application receives the result set.

Example of procedure

Step 1. The tables used in this procedure are,

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    AgentID INT,
    SaleAmount DECIMAL(10, 2),
    SaleDate DATETIME
);

CREATE TABLE Inventory (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Quantity INT
);

CREATE TABLE Commissions (
    CommissionID INT IDENTITY PRIMARY KEY,
    AgentID INT,
    SaleID INT,
    CommissionAmount DECIMAL(10, 2),
    CommissionDate DATETIME
);

INSERT INTO Inventory (ProductID, ProductName, Quantity)
VALUES 
    (1, 'Drone Model A', 100),
    (2, 'Drone Model B', 50);

Step 2. The created procedure with this table is,

CREATE PROCEDURE ProcessSale
    @SaleID INT,
    @AgentID INT,
    @SaleAmount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

        -- Insert a new sales transaction
        INSERT INTO Sales (SaleID, AgentID, SaleAmount, SaleDate)
        VALUES (@SaleID, @AgentID, @SaleAmount, GETDATE());

        -- Update inventory (example logic)
        UPDATE Inventory
        SET Quantity = Quantity - 1
        WHERE ProductID = @SaleID;

        -- Calculate the commission
        DECLARE @Commission DECIMAL(10, 2);
        SET @Commission = dbo.CalculateCommission(@SaleAmount);

        -- Insert commission record
        INSERT INTO Commissions (AgentID, SaleID, CommissionAmount, CommissionDate)
        VALUES (@AgentID, @SaleID, @Commission, GETDATE());

        COMMIT;

        -- Return the result
        SELECT @SaleID AS SaleID, @Commission AS Commission;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        -- Handle the error
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

Upon completion of the stored procedure, the following events are expected to take place.

• Addition of a new entry in the Sales table.
• Modification of the Inventory table, decreasing the product quantity associated with ProductID = 1.
• Invocation of the CalculateCommission function to compute the commission according to the sale amount.
• Insertion of a new record in the Commissions table containing the calculated commission.
• Return of the SaleID and the calculated commission by the stored procedure.

Function in SQL Server

Functions in SQL Server are essential database objects that contain a series of SQL statements and provide either a single value or a table as output. They are utilized for various purposes, such as performing calculations, data manipulation, and generating specific results based on the input parameters given.

Benefits of Functions

• Modularity: Functions provide the ability to encapsulate intricate calculations and utilize them in various queries and applications.
• Streamlined Queries: Through the encapsulation of logic in functions, queries can be streamlined, enhancing readability and ease of maintenance.
• Enhanced Performance: Functions have the potential to enhance performance by minimizing redundant code and optimizing the execution plan.
• Enhanced Security: Functions can safeguard sensitive logic by controlling access to data and operations.

Flow of Function

Description of the above flow

• Client Application: The client application calls upon the function, usually within a SQL query.
• Function Logic: The function carries out its logic, executing calculations or retrieving data.
• Result Value: The function provides either a singular value or a table.
• Obtain Result: The outcome is sent back to the client application.

Types of Functions in SQL Server
 

1. Scalar Functions

• Produce a single value like an integer, string, or date.
• Applicable in expressions like SELECT, WHERE, or ORDER BY clauses.

Example

CREATE FUNCTION dbo.CalculateDiscount
    (@TotalAmount DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @DiscountRate DECIMAL(10, 2) = 0.1; -- 10% discount
    RETURN @TotalAmount * @DiscountRate;
END;

-- Usage:
SELECT dbo.CalculateDiscount(1000.00) AS Discount;

2. Table-Valued Functions (TVFs)

• Return a table as the output.
• Usable in the FROM clause of a SELECT statement.

Example

CREATE FUNCTION dbo.GetSalesByAgent
    (@AgentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT SaleID, SaleAmount, SaleDate
    FROM Sales
    WHERE AgentID = @AgentID
);

-- Usage:
SELECT * FROM dbo.GetSalesByAgent(101);

3. Multi-Statement Table-Valued Functions (mTVFs)

Multi-Statement Table-Valued Functions (mTVFs) in SQL Server are custom functions that produce a table data type and can consist of various statements, including intricate logic and procedural code. In contrast to Inline Table-Valued Functions (iTVFs), which are made up of a single SELECT statement, mTVFs offer greater flexibility and sophistication in their design.

Key Characteristics and Applications of mTVFs

• Multiple Statements: mTVFs can encompass a sequence of SQL statements, such as DECLARE, INSERT, UPDATE, DELETE, and control-of-flow statements (IF, WHILE, etc.).
• Return Type: They consistently yield a table variable specified within the function body.
• Flexibility: They enable intricate processing and transformations that are not achievable with iTVFs.
• Modular Code: mTVFs can be utilized to encapsulate and recycle logic throughout various queries and applications.

Advantages

• Enhanced Readability and Maintainability: mTVFs enhance the readability and maintainability of code by dividing intricate logic into smaller, reusable functions.
• Encapsulation: The function allows for encapsulation of business logic, encouraging code reusability and separation of concerns.

Example

CREATE FUNCTION dbo.GetSalesSummaryByAgent
    (@AgentID INT)
RETURNS @SalesSummary TABLE
(
    SaleID INT,
    TotalAmount DECIMAL(10, 2),
    SaleCount INT
)
AS
BEGIN
    INSERT INTO @SalesSummary (SaleID, TotalAmount, SaleCount)
    SELECT SaleID, SUM(SaleAmount), COUNT(*)
    FROM Sales
    WHERE AgentID = @AgentID
    GROUP BY SaleID;

    RETURN;
END;

-- Usage:
SELECT * FROM dbo.GetSalesSummaryByAgent(101);

Use cases of Procedure

1. Complex Business Logic: If you have to execute intricate business processes that involve multiple steps, transactions, and conditional logic, stored procedures are the way to go.
2. Data Modification: If you need to carry out INSERT, UPDATE, or DELETE operations, procedures can handle these operations directly.
3. Performance: When performance is a primary concern, stored procedures can be optimized and cached by SQL Server, often resulting in faster execution.
4. Output Parameters: If you need to return multiple values or result sets, stored procedures can return multiple output parameters and result sets.
5. Security: If you need to control access to data, procedures can encapsulate the logic and limit direct access to tables.

Use cases of function

1. Scalar Calculations: When you need to perform calculations and return a single value, scalar functions are suitable.
2. Reusable Logic: When you need to encapsulate reusable logic that returns a single value or a table, functions are appropriate.
3. Data Transformation: When you need to transform data within a SELECT statement, functions can be used for this purpose.
4. No Side Effects: When you need to ensure there are no side effects, such as data modifications, functions are the right choice.

When to Use Which?

• Utilize a stored procedure for executing a sequence of tasks involving data modification, intricate business rules, and managing various result sets or output parameters.
• Utilize a function to encapsulate reusable logic that provides a single value or a table, which can be seamlessly incorporated into SELECT statements and other queries.