Parameters in SQL Statements

Using parameters in SQL statements can increase performance and security, help prevent SQL injection attacks, improve code readability, and enable query plan reuse for efficiency. In this blog, let us explore why using parameters in SQL statements is preferred and the different methods of using parameters in SQL Server.

Table of Contents:

• What are SQL Parameters?
• Advantages of Using Parameters in SQL Statements
• Methods of Using Parameters in SQL Statements in SQL Server
  • Method 1: Using Variables in SQL Server
  • Method 2: Using sp_executesql for Dynamic SQL with parameters in SQL Server
  • Method 3: Using Table-Valued functions in SQL Server
  • Method 4: Using OUTPUT Parameters with Stored Procedure in SQL Server
  • Method 5: Using Table-Valued Parameters in SQL Server
• Performance Comparison of Each Method
• Best Practices
• Conclusion

What are SQL Parameters?

SQL parameters are locations in SQL statements that hold values during runtime without inserting the values directly into the queries. By separating the data from SQL logic, they help improve performance and make SQL more secure.

Example:

Let us consider a simple example for a better understanding of SQL Parameters

SELECT * FROM Orders WHERE CustomerID = @CustomerID;

Here, the @CustomerID is a parameter that will be assigned a value when the query runs.

Advantages of Using Parameters in SQL Statements

• Prevents SQL Injection: SQL injection attacks are averted by parameters, which ensure that user input is treated as data instead of executable code. 
• Enhances the Performance of Query: In SQL Server, when a parameterized query is executed, it utilizes an existing execution plan, which helps to avoid the overhead of parsing and creating optimization plans for queries. 
• Prevents Code Duplication: Always define one parameterized query that accepts more than one input value.

Master Parameters in SQL Statements and Other Key Concepts

SQL Certification Training

Explore Program

Methods of Using Parameters in SQL Statements in SQL Server

Using parameters in SQL Statements will increase performance, security, and maintainability. There are a few methods, like using variables in T-SQL, table-valued parameters, etc.

Before getting started with the methods, let us create an Orders table and insert some values into it that can be used as an example for the following methods

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10,2)
);

INSERT INTO Orders (CustomerID, OrderDate, Amount)  
VALUES  
    (101, '2024-03-01', 150.00),  
    (102, '2024-03-02', 200.50),  
    (103, '2024-03-03', 320.75),  
    (101, '2024-03-04', 180.25),  
    (104, '2024-03-05', 500.00),  
    (102, '2024-03-06', 220.00),  
    (103, '2024-03-07', 315.50),  
    (101, '2024-03-08', 400.00); 
 
SELECT * FROM Orders;

This is how the table looks once it is created and values are inserted.

Method 1: Using Variables in SQL Server

In SQL Server, the variables can be used to store the temporary results or values that can be used within a query later. 

Syntax:

DECLARE @VariableName DataType;
SET @VariableName = Value;
DECLARE @VariableName DataType;
SELECT @VariableName = Value FROM TableName WHERE Condition;

Example:

DECLARE @CustomerID INT = 101;
SELECT * 
FROM Orders 
WHERE CustomerID = @CustomerID;

Output:

Explanation: Here, the DECLARE @CustomerID INT = 101 statement declares a variable and assigns it a value of 101. Thus, the SELECT statement retrieves all orders where CustomerID = 101.

Method 2: Using sp_executesql for Dynamic SQL with parameters in SQL Server

In SQL Server, sp_executesql is a system-stored procedure that is used to execute the dynamic SQL statements safely with parameterization.

Syntax:

DECLARE @sql NVARCHAR(MAX);
DECLARE @ParamDataType NVARCHAR(MAX);
DECLARE @VariableName DataType;
SET @sql = N'Dynamic SQL Query';
SET @ParamDataType = N'@VariableName DataType';
EXEC sp_executesql @sql, @ParamDataType, @VariableName;

Example:

DECLARE @sql NVARCHAR(MAX);
DECLARE @CustomerID INT = 102;
SET @sql = N'SELECT * FROM Orders WHERE CustomerID = @CID';
EXEC sp_executesql @sql, N'@CID INT', @CustomerID;

Output:

Explanation: Here, this query filters orders by CustomerID = 102 by dynamically executing a parameterized SQL statement using sp_executesql.

Method 3: Using Table-Valued functions in SQL Server

In SQL Server, a table-valued function is a user-defined function that returns a table, and it supports code reusability along with enhanced performance.

Syntax:

CREATE FUNCTION FunctionName(@Parameter DataType)  
RETURNS TABLE  
AS  
RETURN  
(  
    SQL Query Returning a Table  
);

Example:

GO  
CREATE FUNCTION GetOrdersByCustomer(@CustomerID INT)  
RETURNS TABLE  
AS  
RETURN  
(  
    SELECT * FROM Orders WHERE CustomerID = @CustomerID  
);  
GO  
SELECT * FROM GetOrdersByCustomer(104);

Output:

Explanation: Here, the CREATE FUNCTION GetOrdersByCustomer(@CustomerID INT)  returns a table filtered by CustomerID = 104. 

Method 4: Using OUTPUT Parameters with Stored Procedure in SQL Server

OUTPUT parameters allow a stored procedure to return values to the calling query. This can be used when we need to return one value instead of a complete result set.

Syntax:

CREATE PROCEDURE ProcedureName  
    @InputParameter DataType,  
    @OutputParameter DataType OUTPUT  
AS  
BEGIN  
    -- SQL Logic to Assign Output Value
    SELECT @OutputParameter = SomeValue FROM TableName WHERE Condition;  
END;

Example:

GO  
CREATE PROCEDURE GetTotalOrderAmount  
    @CustomerID INT,  
    @TotalAmount DECIMAL(10,2) OUTPUT  
AS  
BEGIN  
    SELECT @TotalAmount = SUM(Amount)  
    FROM Orders  
    WHERE CustomerID = @CustomerID;  
END;  

GO  
DECLARE @Total DECIMAL(10,2);  
EXEC GetTotalOrderAmount @CustomerID = 103, @TotalAmount = @Total OUTPUT;  
SELECT @Total AS TotalOrderAmount;

Output:

Explanation: Here, the @TotalAmount DECIMAL(10,2) is an output parameter that will store the SUM(Amount). The @total variable receives the computed total from the procedure.

Method 5: Using Table-Valued Parameters in SQL Server

This method allows us to pass the entire table as a parameter to a stored procedure or function.

Syntax:

CREATE TYPE TypeName AS TABLE  
(Column1 DataType, Column2 DataType, ...);
CREATE PROCEDURE ProcedureName  
    @ParamName TypeName READONLY  
AS  
BEGIN  
    INSERT INTO TableName (Column1, Column2, ...)  
    SELECT Column1, Column2, ... FROM @ParamName;  
END;

Example:

GO  
CREATE TYPE OrderTableType AS TABLE  
(  
    OrderID INT,  
    CustomerID INT,  
    OrderDate DATE,  
    Amount DECIMAL(10,2)  
);  

GO  
CREATE PROCEDURE InsertMultipleOrders  
    @OrderList OrderTableType READONLY  
AS  
BEGIN  
    INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount)  
    SELECT OrderID, CustomerID, OrderDate, Amount FROM @OrderList;  
END;  

GO  
DECLARE @NewOrders OrderTableType;  
INSERT INTO @NewOrders (OrderID, CustomerID, OrderDate, Amount)  
VALUES  
    (10, 102, '2024-03-10', 250.50),  
    (11, 103, '2024-03-11', 300.75),  
    (12, 104, '2024-03-12', 450.00);  

EXEC InsertMultipleOrders @OrderList = @NewOrders;  
SELECT * FROM Orders;  

Output:

Explanation: Here, the DECLARE @NewOrders OrderTableType statement declares and populates a TVP Variable, calls the procedure, and verifies the insertion by executing: SELECT * FROM Orders.

Performance Comparison of Each Method

Best Practices

• Specify strongly typed parameters to improve the optimization of a query and avoid implicit conversion. 

• Prefer sp_executesql over EXEC for dynamic queries as it allows for parameterized dynamic SQL execution and for caching the execution plan, as a better option than EXEC.

• Use TVP (Table Valued Parameters) for Bulk Data Operations instead of passing multiple values as comma-separated strings.

• Use TVP for performance improvement using VARCHAR(MAX) or NVARCHAR(MAX) unless necessary usage of a large datatype can preclude plan reuse and will increase your memory footprint.

Mastering SQL Key Concepts

SQL Free Training Course

Explore Program

Conclusion

Applying parameters to an SQL query is a good practice that boosts the security, performance, and readability of the query. Other ways of managing data include stored procedures, sp_executesql, output parameters, and table-valued parameters (TVP). All of these solutions help in data processing and improve the speed and efficiency of data managed in the queries.

In this blog, you have gained knowledge on why using parameters is always preferred in SQL statements.

Take your skills to the next level by enrolling in our SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.

Parameters in SQL statements – FAQs