SQL procedure with parameters

A SQL Server procedure with parameters is a stored database object that allows you to encapsulate a sequence of SQL statements and execute them as a single unit. Parameters are placeholders within the procedure that can accept values when the procedure is called, making it flexible and reusable for different scenarios. SQL Server procedures with parameters offer several advantages, such as code reusability, security, and improved performance.

Example

Here’s an example of creating a simple SQL Server procedure with parameters:

-- Create a new procedure with two parameters
CREATE PROCEDURE usp_GetEmployeeInfo
    @EmployeeID INT,
    @DepartmentID INT
AS
BEGIN
    -- SQL statements to retrieve employee information based on parameters
    SELECT FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID
        AND DepartmentID = @DepartmentID;
END;

In this example, we’ve created a procedure named usp_GetEmployeeInfo with two parameters: @EmployeeID and @DepartmentID. The procedure selects employee information from the “Employees” table based on the provided parameters.

To execute this procedure and pass values to its parameters, you can use the EXEC statement like this:

-- Execute the procedure with specific parameter values
EXEC usp_GetEmployeeInfo @EmployeeID = 12345, @DepartmentID = 1;

Here, we’re calling the usp_GetEmployeeInfo procedure with specific values for @EmployeeID and @DepartmentID. The procedure will return the relevant employee information based on these parameters.

Benefits

Code Reusability: Procedures allow you to encapsulate and reuse SQL logic, reducing code duplication and promoting maintainability.

Improved Performance: SQL Server can optimize the execution plan of stored procedures, leading to better performance compared to executing individual SQL statements.

Security: You can grant specific permissions to procedures, restricting direct table access and enhancing data security.

Parameterization: Parameters provide flexibility in filtering and customizing query results without altering the procedure’s code.

Reduced SQL Injection Risk: Using parameters helps prevent SQL injection attacks, as input values are treated as data rather than executable SQL code.

Centralized Logic: Procedures centralize database logic, making it easier to manage and update database operations.

In summary, SQL Server procedures with parameters are a powerful tool for encapsulating and executing SQL logic with flexibility and security. They enhance code reusability, improve performance, and help protect your database from security vulnerabilities.