SQL Interview Questions

Stored procedures are precompiled collections of one or more SQL statements stored within the database. They allow for complex operations and logic to be encapsulated within a single callable routine, which can be executed repeatedly by the database server. Stored procedures are commonly used to encapsulate business logic, perform repetitive tasks, and ensure consistent execution of SQL statements.

Key Characteristics of Stored Procedures

1. Precompiled: Stored procedures are precompiled and stored in the database, which can lead to performance improvements because the SQL statements do not need to be parsed and compiled each time they are executed.
2. Reusable: Once created, stored procedures can be executed multiple times with different parameters, promoting code reuse and reducing redundancy.
3. Modular: They encapsulate business logic in a single, maintainable unit, which can simplify complex operations and improve code organization.
4. Security: Stored procedures can enhance security by controlling access to underlying data through parameterized execution, reducing the risk of SQL injection attacks.

Benefits of Using Stored Procedures

1. Performance: Precompilation and caching of execution plans can lead to faster execution compared to dynamic SQL.
2. Maintainability: Centralizing business logic in stored procedures makes it easier to update and maintain the code.
3. Consistency: Ensures consistent execution of SQL statements, reducing the risk of errors due to ad-hoc queries.
4. Security: Provides an additional layer of security by restricting direct access to database tables and allowing controlled access through parameters.

Example of a Stored Procedure

Here's an example of a simple stored procedure that retrieves employee details based on a department ID.

Creating the Stored Procedure

CREATE PROCEDURE GetEmployeesByDepartment
  @DepartmentID INT
AS
BEGIN
  SELECT EmployeeID, FirstName, LastName, Title
  FROM Employees
  WHERE DepartmentID = @DepartmentID;
END;

Executing the Stored Procedure

EXEC GetEmployeesByDepartment @DepartmentID = 5;

In this example:

• The GetEmployeesByDepartment stored procedure takes a single parameter, @DepartmentID.
• It retrieves employee details from the Employees table where the DepartmentID matches the provided parameter.

Advanced Features of Stored Procedures

1. Parameters: Stored procedures can accept input parameters, output parameters, and even return multiple result sets.

   • Input Parameters: Allow passing values to the procedure.
   • Output Parameters: Allow returning values from the procedure.
   • Return Values: Procedures can return a single integer value, typically used for status codes.

2. Conditional Logic: Stored procedures can include complex logic using SQL control-of-flow statements like IF, ELSE, WHILE, and CASE.

   CREATE PROCEDURE UpdateEmployeeSalary
     @EmployeeID INT,
     @NewSalary DECIMAL(10, 2)
   AS
   BEGIN
     IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)
     BEGIN
       UPDATE Employees
       SET Salary = @NewSalary
       WHERE EmployeeID = @EmployeeID;
     END
     ELSE
     BEGIN
       PRINT 'Employee not found.';
     END
   END;

3. Transaction Management: Stored procedures can handle transactions to ensure data integrity.

   CREATE PROCEDURE TransferFunds
     @FromAccountID INT,
     @ToAccountID INT,
     @Amount DECIMAL(10, 2)
   AS
   BEGIN
     BEGIN TRANSACTION;
   
     UPDATE Accounts
     SET Balance = Balance - @Amount
     WHERE AccountID = @FromAccountID;
   
     UPDATE Accounts
     SET Balance = Balance + @Amount
     WHERE AccountID = @ToAccountID;
   
     IF @@ERROR <> 0
     BEGIN
       ROLLBACK TRANSACTION;
       PRINT 'Error occurred. Transaction rolled back.';
     END
     ELSE
     BEGIN
       COMMIT TRANSACTION;
       PRINT 'Transaction completed successfully.';
     END
   END;

4. Error Handling: Use TRY...CATCH blocks to handle exceptions and errors within stored procedures.

   CREATE PROCEDURE SafeDeleteEmployee
     @EmployeeID INT
   AS
   BEGIN
     BEGIN TRY
       DELETE FROM Employees
       WHERE EmployeeID = @EmployeeID;
     END TRY
     BEGIN CATCH
       PRINT 'An error occurred. The employee could not be deleted.';
     END CATCH;
   END;

Conclusion

Stored procedures are a powerful feature of relational databases that allow you to encapsulate and manage complex SQL logic within the database itself. They offer benefits such as improved performance, enhanced security, easier maintenance, and consistent execution. By understanding and leveraging stored procedures, you can create more efficient, secure, and maintainable database applications.