Mastering Stored Procedures in SQL Server

Mastering Stored Procedures in SQL Server

by
Spread the love
         

A stored procedure is a precompiled collection of SQL statements stored on the database server. It allows you to encapsulate logic, reduce redundancy, and improve performance.

Key Benefits of Stored Procedures:

  1. Performance: Precompiled and optimized, leading to faster execution.
  2. Security: Helps enforce access controls.
  3. Reusability: Write once, and use multiple times.
  4. Maintainability: Centralized logic makes updates easier.

Let’s explore stored procedures with real-world examples

Creating Stored Procedures

Use the CREATE PROCEDURE statement to create a stored procedure in SQL Server. Here’s a basic example:

CREATE PROCEDURE [dbo].[AddDepartment]
    @Name NVARCHAR(50),
    @Location NVARCHAR(50)
AS
BEGIN
    INSERT INTO Department (DepartmentName, Location)
    VALUES (@Name,@Location);
END;

Step-by-Step Explanation:

  1. Procedure Name: AddDepartment is the name of the procedure.
  2. Parameters: Accepts three input parameters for the department details.
  3. Logic: Inserts a new record into the Department table.

Executing Stored Procedures

To execute a stored procedure, use the EXEC statement or the EXECUTE keyword.

EXEC AddDepartment @Name = 'Admin', @Location = 'London';

This will add a new department.

Input and Output Parameters

Stored procedures can accept input parameters, return output parameters, or both.

CREATE PROCEDURE CalculateEmployeeSalary
    @EmpId INT,
    @TotalSalary DECIMAL(10, 2) OUTPUT
AS
BEGIN

    SELECT @TotalSalary = Salary + Bonus
    FROM EmployeeSalary
    WHERE EmployeeID = @EmpId;

END;

Execute with Output Parameter:

DECLARE @Total DECIMAL(10, 2);
EXEC CalculateEmployeeSalary @EmpId = 1, @TotalSalary = @Total OUTPUT;
Select @Total as EmpSalary;

Nested Procedures

Stored procedures can call other procedures, enabling modular design.

ALTER PROCEDURE [dbo].[AddDepartment]
    @Name NVARCHAR(50),
    @Location NVARCHAR(50)
AS
BEGIN

        INSERT INTO Department (DepartmentName, Location)
        VALUES (@Name,@Location);

       Declare @DeptId int = SCOPE_IDENTITY();

	-- Calling Add Employee SP
	Exec AddEmployee 'David','Warner','david@gmail.com','888-999-6666','2025-01-01',@DeptId

END;

Error Handling

Use TRY…CATCH blocks to handle errors effectively.

ALTER PROCEDURE [dbo].[AddDepartment]
    @Name NVARCHAR(50),
    @Location NVARCHAR(50)
AS
BEGIN

BEGIN TRY

	INSERT INTO Department (DepartmentName, Location) VALUES (@Name,@Location);
	
        Declare @DeptId int = SCOPE_IDENTITY();

	-- Calling Add Employee SP
	Exec AddEmployee 'David','Warner','david@gmail.com','888-999-6666','2025-01-01',@DeptId

END TRY
BEGIN CATCH

        PRINT 'Error: ' + ERROR_MESSAGE();

 END CATCH
END;

Dynamic SQL

Generate and execute SQL queries dynamically within a procedure.

CREATE PROCEDURE [dbo].[GetEmployeeData]
    @EmailId NVARCHAR(100),
    @DepartmentId int
AS
BEGIN

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT * FROM Employee WHERE ';

	If @EmailId != ''
	Begin
		SET @SQL += ' Email =''' + @EmailId + ''' and ' ;
	End

	If @DepartmentId != 0
	Begin
		SET @SQL += ' DepartmentID =' + Cast(@DepartmentId as nvarchar(50));
	End

    EXEC sp_executesql @SQL;

END;

Execute Dynamic SQL:

Exec GetEmployeeData 'david@gmail.com', 11

Transactions In Stored Procedures

Transactions are used to manage the logical units of work that ensure data integrity and consistency within a database. The statements BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION are key to implementing transaction control in SQL Server.

1. BEGIN TRANSACTION

  • Purpose: Marks the starting point of a transaction. From this point, SQL Server begins to treat all subsequent SQL statements as a single unit of work.
  • Use Case: You use BEGIN TRANSACTION when you want to ensure that a set of operations either succeed or fail as a whole.
BEGIN TRANSACTION;
INSERT INTO Orders (CustomerId, OrderDate) VALUES (1, GETDATE());

2. COMMIT TRANSACTION

  • Purpose: Confirms the changes made during the transaction. Once committed, the changes become permanent in the database.
  • Key Point: A committed transaction cannot be undone.
  • Use Case: You use COMMIT TRANSACTION at the end of a successful transaction to finalize the changes.
BEGIN TRANSACTION;

INSERT INTO Orders (CustomerId, OrderDate) VALUES (1, GETDATE());

COMMIT TRANSACTION;

3. ROLLBACK TRANSACTION

  • Purpose: Undoes all changes made during the transaction, returning the database to its previous state before BEGIN TRANSACTION was called.
  • Key Point: A rollback cancels the entire transaction and ensures no partial changes are made.
  • Use Case: Use ROLLBACK TRANSACTION if an error occurs or a condition fails, and you want to revert the database to its original state.
BEGIN TRANSACTION;
INSERT INTO Orders (CustomerId, OrderDate) VALUES (1, GETDATE());

IF @@ERROR <> 0  -- Check for any error
BEGIN

    ROLLBACK TRANSACTION;
    PRINT 'Transaction failed!';

END
ELSE
BEGIN

    COMMIT TRANSACTION;
    PRINT 'Transaction succeeded!';

END

Real-World Example: Processing an Order

CREATE PROCEDURE ProcessOrder
    @CustomerId INT,
    @OrderDate DATE,
    @ProductId INT,
    @Quantity INT,
    @Price DECIMAL(10, 2)
AS
BEGIN

    BEGIN TRANSACTION;

    BEGIN TRY
        -- Insert Order
        INSERT INTO Orders (CustomerId, OrderDate)
        VALUES (@CustomerId, @OrderDate);

        -- Get the newly created OrderId
        DECLARE @OrderId INT = SCOPE_IDENTITY();

        -- Insert Order Details
        INSERT INTO OrderDetails (OrderId, ProductId, Quantity, Price)
        VALUES (@OrderId, @ProductId, @Quantity, @Price);

        -- Commit the transaction
        COMMIT TRANSACTION;
        PRINT 'Order processed successfully!';
    END TRY

    BEGIN CATCH
        -- Rollback the transaction in case of any error
        ROLLBACK TRANSACTION;
        PRINT 'Transaction failed: ' + ERROR_MESSAGE();
    END CATCH

END;
  • BEGIN TRANSACTION: Starts a transaction.
  • COMMIT TRANSACTION: Finalizes and saves changes to the database permanently.
  • ROLLBACK TRANSACTION: Reverts all changes made since BEGIN TRANSACTION in case of errors or issues.

These commands ensure that database operations are consistent, reliable, and safe, especially in complex workflows where multiple steps depend on each other.

Difference Between Functions And Stored Procedures

FeatureFunctionStored Procedure
DefinitionA reusable SQL code block that returns a value.A reusable SQL code block that performs an action.
Return TypeMust return a value (scalar, table, or result set).Can return zero, one, or multiple result sets.
Return KeywordAlways uses RETURN to send back a value.Uses OUT parameters or RETURN for status codes.
ParametersOnly input parameters are allowed.Supports both input and output parameters.
ExecutionInvoked within SQL statements (e.g., SELECT).Executed using EXEC or EXECUTE keyword.
Transaction HandlingCannot use TRY…CATCH or manage transactions.Can use TRY…CATCH and manage transactions.
DML StatementsLimited to SELECT statements (read-only).Can include INSERT, UPDATE, DELETE, etc.
Side EffectsCannot modify database state.Can modify database state (e.g., insert rows).
Error HandlingLimited error handling.Supports robust error handling with TRY…CATCH.
Usage in QueriesCan be used in SELECT, WHERE, HAVING, etc.Cannot be directly used in queries.
Compiled StateCompiled every time it is executed.Precompiled and stored in the database.
PerformanceOptimized for calculations and returning values.Better for complex logic and database operations.
Temporary StorageCannot use temporary tables.Can use temporary tables.
NestingCan be called inside another function or procedure.Can call other procedures or functions.
OutputReturns a single value, table, or result set.Can return multiple result sets or affect the database state.
Example Use CaseCalculating totals or reusable business logic.Executing complex logic, managing transactions.

Advantages of Stored Procedures

  1. Performance Improvement
    • Stored procedures are precompiled and stored in the database, reducing execution time for repetitive tasks.
    • Execution plans are cached, making subsequent executions faster.
  2. Enhanced Security
    • Access to database tables can be restricted, and users can interact with data only through stored procedures.
    • Helps prevent SQL injection by using parameterized queries.
  3. Reduced Network Traffic
    • Since stored procedures execute on the server, only the procedure call and results are sent across the network, reducing data transfer.
  4. Reusability and Maintainability
    • Encapsulates business logic in one place, making it reusable across multiple applications.
    • Updates and fixes can be done in the database without changing the application code.
  5. Centralized Business Logic
    • Business rules and validation logic can be centralized, ensuring consistency across applications.
  6. Error Handling
    • Stored procedures support robust error handling using TRY…CATCH blocks.
  7. Modularity
    • Code can be organized into small, manageable procedures for easier development and debugging.
  8. Transaction Management
    • Allows managing transactions (BEGIN TRAN, COMMIT, ROLLBACK) within the procedure for better control.

Disadvantages of Stored Procedures

  1. Database Dependency
    • Stored procedures are tied to a specific database, making the application less portable across database systems.
  2. Complex Debugging
    • Debugging stored procedures can be challenging compared to debugging application-level code.
  3. Version Control Issues
    • Unlike application code, stored procedures are not easily integrated with standard version control systems.
  4. Performance Bottlenecks
    • Overusing stored procedures for compute-intensive logic can overload the database server.
  5. Maintenance Overhead
    • Large or poorly designed procedures can be hard to understand and maintain.
  6. Limited Flexibility
    • Changing stored procedure logic often requires altering the database, which might not be as agile as application-level changes.
  7. Testing Complexity
    • Proper testing requires a fully set-up database environment with realistic data.
  8. Scalability Concerns
    • Heavy reliance on stored procedures can make scaling out the application more difficult, especially in distributed environments.
  9. Hidden Logic
    • Business logic embedded in stored procedures may not be visible to developers working on the application layer, leading to knowledge silos.
  10. Learning Curve
    • Writing efficient and secure stored procedures requires a deeper understanding of SQL and database management.

Also Read,


Spread the love