Understanding Stored Procedures in MSSQL

What is a Stored Procedure?

A stored procedure is a group of SQL statements that are compiled and saved in the database. Instead of writing the same SQL query repeatedly, you can create a stored procedure and reuse it. Stored procedures can also accept inputs and give outputs.

Why Use Stored Procedures?

1. Faster Execution: Stored procedures are precompiled. This means they run faster than regular SQL queries.
2. Reusability: Once created, they can be used multiple times.
3. Security: Users can execute stored procedures without accessing the database directly.
4. Error Handling: Stored procedures can include error-handling logic.

How to Create a Stored Procedure

The basic syntax for creating a stored procedure is:

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,      -- Input parameter
    @Parameter2 DataType OUTPUT -- Output parameter (optional)
AS
BEGIN
    -- SQL statements
END;

Let us look at some examples to understand stored procedures better.

Example 1: A Simple Stored Procedure

This stored procedure retrieves all the employees from a table called Employees.

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;

How to Execute:

EXEC GetAllEmployees;

Example 2: Stored Procedure with Input Parameter

This stored procedure retrieves employees from a specific department.

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentName NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Employees
    WHERE Department = @DepartmentName;
END;

How to Execute:

EXEC GetEmployeesByDepartment @DepartmentName = 'IT';

Example 3: Stored Procedure with Input and Output Parameters

This procedure calculates the total salary of a department and returns it as an output.

CREATE PROCEDURE GetTotalSalaryByDepartment
    @DepartmentName NVARCHAR(50),
    @TotalSalary DECIMAL(18,2) OUTPUT
AS
BEGIN
    SELECT @TotalSalary = SUM(Salary)
    FROM Employees
    WHERE Department = @DepartmentName;
END;

How to Execute:

DECLARE @TotalSalary DECIMAL(18,2);

EXEC GetTotalSalaryByDepartment
    @DepartmentName = 'HR',
    @TotalSalary = @TotalSalary OUTPUT;

PRINT @TotalSalary;

ALTER PROC GetGrossSalary
(
	@EmployeeId INT = 0,
	@GrossSalary DECIMAL(10,2)=0.00 output,
	@GrossSalary1 DECIMAL(10,2)=0.00 output
) AS
select @GrossSalary=GrossSalary,@GrossSalary1=GrossSalary from PAY_SalaryDetails sd where sd.ToDate is null and sd.EmployeeID=@EmployeeId
--return @GrossSalary -- only return signal value. 
go
declare @sal decimal(10,2)=0.0
declare @sal1 decimal(10,2)=0.0
exec GetGrossSalary 155,@sal output,@sal1 output --this type sntax used when return not given 
select @sal,@sal1
--exec @sal=  GetGrossSalary 155,@sal output --this type of syntax used when return given like --return @GrossSalary 
--select @sal,@sal1

Example 4: Stored Procedure with Error Handling

This procedure handles errors during data insertion.

CREATE PROCEDURE InsertEmployee
    @Name NVARCHAR(50),
    @Department NVARCHAR(50),
    @Salary DECIMAL(18,2)
AS
BEGIN
    BEGIN TRY
        INSERT INTO Employees (Name, Department, Salary)
        VALUES (@Name, @Department, @Salary);
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

How to Execute:

EXEC InsertEmployee @Name = 'John Doe', @Department = 'Finance', @Salary = 55000.00;

Conclusion

Stored procedures are a powerful feature of MSSQL. They make database operations faster, safer, and easier to maintain. Whether you need to run complex queries, enforce business rules, or secure your data, stored procedures can help you achieve your goals efficiently.

Basic Questions and Answers

1. What is a stored procedure in MSSQL?
   A stored procedure is a precompiled set of SQL statements stored in the database. It is reusable and can be executed as needed.
2. How do you create and execute a stored procedure?
   • Create: Use the CREATE PROCEDURE statement.
     Example: CREATE PROCEDURE GetAllEmployees AS BEGIN SELECT * FROM Employees; END;
   • Execute: Use the EXEC keyword.
     Example: EXEC GetAllEmployees;
3. What are the advantages of using stored procedures?
   • Faster execution (precompiled).
   • Reusable.
   • Enhances security by restricting direct database access.
   • Easier to maintain centralized logic.
4. What is the difference between a stored procedure and a function?
   • Stored procedures can perform actions (e.g., INSERT, UPDATE) and may or may not return a value.
   • Functions must return a value and cannot perform actions like INSERT or UPDATE.
5. How do input and output parameters work in a stored procedure?
   • Input parameters are used to pass values into a stored procedure.
   • Output parameters return values from the procedure.
     Example:
   CREATE PROCEDURE ExampleProcedure (@InputParam INT, @OutputParam INT OUTPUT) AS BEGIN SET @OutputParam = @InputParam * 2; END;

Intermediate Questions and Answers

1. Can you explain error handling in stored procedures?
   Error handling is done using TRY...CATCH blocks.
   Example: BEGIN TRY -- SQL statements END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH;
2. How can you call a stored procedure within another stored procedure?
   Use the EXEC keyword.
   Example: CREATE PROCEDURE ProcedureA AS BEGIN EXEC ProcedureB; END;
3. What is the difference between a stored procedure and a view?
   • A stored procedure can include multiple SQL statements and logic (e.g., loops, conditions).
   • A view is a virtual table representing a single SELECT query.
4. How do you implement transactions in stored procedures?
   Use BEGIN TRAN, COMMIT, and ROLLBACK.
   Example: BEGIN TRAN; INSERT INTO Employees VALUES ('John Doe', 'IT'); IF @@ERROR <> 0 ROLLBACK; ELSE COMMIT;
5. How do you optimize a stored procedure for better performance?
   • Use indexed columns in queries.
   • Avoid using SELECT *.
   • Keep logic simple.
   • Use query execution plans to analyze performance.

Advanced Questions and Answers

1. What are dynamic SQL and parameterized queries in stored procedures?
   • Dynamic SQL: SQL built at runtime. Example: EXEC('SELECT * FROM Employees WHERE Department = ''' + @DeptName + '''');
   • Parameterized queries: Prevent SQL injection. Example: EXEC sp_executesql N'SELECT * FROM Employees WHERE Department = @Dept', N'@Dept NVARCHAR(50)', @Dept = @DeptName;
2. How do you debug a stored procedure in MSSQL?
   • Use the SQL Server Management Studio (SSMS) debugger.
   • Add PRINT statements to check intermediate values.
   • Use SQL Profiler to trace procedure execution.
3. Explain how you can use TRY...CATCH in stored procedures.
   The TRY...CATCH block captures errors and prevents the procedure from failing entirely.
   Example: BEGIN TRY INSERT INTO Employees VALUES ('Jane Doe', 'HR'); END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH;
4. How can you implement conditional logic inside a stored procedure?
   Use IF...ELSE or CASE.
   Example: IF @DeptName = 'IT' BEGIN SELECT * FROM ITDepartment; END ELSE BEGIN SELECT * FROM Employees; END;
5. How do you manage complex business logic in a stored procedure?
   • Break logic into smaller, reusable stored procedures.
   • Use comments to document logic.
   • Implement modular design with input/output parameters.