SQL Parameters

This document provides a comprehensive guide to using parameters in SQL Server. Parameters are essential for creating dynamic, secure, and efficient database queries and stored procedures.

What are SQL Parameters?

SQL parameters are placeholders within a SQL statement that can be replaced with actual values when the statement is executed. They allow you to pass variables into your SQL code without directly embedding them in the query string. This practice is crucial for:

Security: Prevents SQL injection attacks by separating code from data.
Performance: Enables query plan reuse by the database engine.
Readability: Makes SQL code cleaner and easier to understand.
Maintainability: Simplifies modification of query values.

Types of Parameters

SQL Server supports several ways to handle parameters, primarily through:

Query Parameters: Used in ad-hoc queries and within application code.
Stored Procedure Parameters: Defined as part of a stored procedure's signature.

Using Query Parameters

In application development, you typically use parameters with your database connector. The syntax for defining a parameter varies slightly depending on the programming language and library used, but the concept remains the same.

Example (Conceptual C# with ADO.NET):


string connectionString = "YourConnectionString";
string query = "SELECT * FROM Products WHERE Category = @Category AND Price > @MinPrice;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(query, connection);

    // Add parameters
    command.Parameters.AddWithValue("@Category", "Electronics");
    command.Parameters.AddWithValue("@MinPrice", 500.00);

    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    // ... process results ...
}

In the example above:

@Category and @MinPrice are parameter placeholders.
AddWithValue is a convenient method to add parameters and their values. It infers the data type. For better control, you can explicitly specify the SqlDbType.

Stored Procedure Parameters

Stored procedures are precompiled SQL code stored on the database server. They can accept input, output, and input/output parameters.

Defining a Stored Procedure with Parameters:


CREATE PROCEDURE GetProductsByCategoryAndPrice
    @CategoryName NVARCHAR(50),
    @MinimumPrice DECIMAL(10, 2)
AS
BEGIN
    SELECT ProductID, ProductName, Price
    FROM Products
    WHERE Category = @CategoryName AND Price >= @MinimumPrice;
END
GO

Executing a Stored Procedure with Parameters:


EXEC GetProductsByCategoryAndPrice @CategoryName = 'Clothing', @MinimumPrice = 75.00;

Input vs. Output Parameters

Input Parameters: Pass values into the stored procedure. This is the most common type. (e.g., @CategoryName in the example above).
Output Parameters: Allow a stored procedure to return a single value or status indicator back to the caller.

Example with Output Parameter:


CREATE PROCEDURE GetProductCountByCategory
    @CategoryName NVARCHAR(50),
    @ProductCount INT OUTPUT
AS
BEGIN
    SELECT @ProductCount = COUNT(*)
    FROM Products
    WHERE Category = @CategoryName;
END
GO

-- Executing with an output parameter (example syntax, might vary by client)
DECLARE @Count INT;
EXEC GetProductCountByCategory @CategoryName = 'Books', @ProductCount = @Count OUTPUT;
SELECT @Count AS NumberOfBooks;

Parameter Data Types

It is crucial to use the correct data types for your parameters to ensure data integrity and prevent errors. SQL Server provides a wide range of data types, such as INT, VARCHAR, NVARCHAR, DECIMAL, DATETIME, etc. When using `AddWithValue` in application code, the library often tries to infer the type, but it's best practice to be explicit, especially for large text, binary data, or specific date/time formats.

Best Practices for Parameter Usage

Always use parameterized queries or stored procedures with parameters.
Define explicit data types for parameters when possible, especially in stored procedures.
Use NVARCHAR for string parameters to support Unicode characters.
Be mindful of parameter length and precision for data types like VARCHAR and DECIMAL.
Validate input data in your application layer before passing it to the database.