Declare Variable for a Query String

sql

dynamic-sql

parameterized-queries

sql-injection

byNikita Barsukov·Nov 29, 2024

To create a query string variable in SQL, you first need to DECLARE it, SET a value for it, and finally pass it to the EXEC clause for execution:

DECLARE @Query NVARCHAR(MAX);
SET @Query = 'SELECT * FROM Table WHERE Column = Condition';
EXEC(@Query);

Using this, you can create complex queries on the fly, which allows you to increase the flexibility of your SQL scripts. However, be mindful about SQL injection risks that could accompany dynamic SQL.

Getting to grips with dynamic SQL

Dynamic SQL comes to the rescue when dealing with complex queries where conditions or schema elements are dynamic and not known at compile time. It allows you to construct SQL at the runtime, thus adding tremendous flexibility to your code.

Using sp_executesql for parameter safety

sp_executesql provides a more secure way to execute dynamic SQL, where it allows parameterized queries reducing SQL injection risk. Just like this:

DECLARE @SQL NVARCHAR(MAX), @ParamDefinition NVARCHAR(MAX), @ID INT;
SET @SQL = N'SELECT * FROM Employees WHERE ID = @EmpID';
SET @ParamDefinition = N'@EmpID INT';

SET @ID = 1; -- Let's find the top banana 🍌
EXEC sp_executesql @SQL, @ParamDefinition, @EmpID = @ID;

Risks and Limitations

Stay conscious of the limitations of dynamic SQL, which can lead to debugging issues and possible performance overheads. Also, always be on alert for security vulnerabilities.

Declaring and using variables

Variables are like placeholders within your SQL query that can be declared once and reused multiple times. The DATEADD function, for example, can be extremely handy when dealing with date-range queries.

DECLARE @StartDate DATE = '2021-01-01';
DECLARE @EndDate DATE;
SET @EndDate = DATEADD(MONTH, 1, @StartDate); -- Back to the future! 

SELECT * FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate;

Advanced scenario?

In challenging situations, consider using user-defined functions (UDFs) or stored procedures, which can handle complex operations such as list unpacking. This allows more control than dynamic SQL and ensures your code is secure and performances are up to the mark.

Exploring alternatives

Alternatives like table-valued parameters, common table expressions, and inline variables within static SQL should be considered whenever possible to limit our dependency on dynamic SQL. This helps keep the codebase maintainable and squeaky clean.

Mastering Queries without dynamic SQL

One can often avoid dynamic SQL by merely structuring queries differently. This could involve joining tables, using subqueries, or employing temporary tables.

List unpacking with joins

If you need to filter based on a list, consider using a join operation with the table containing your filtering conditions (a temporary table, for example), rather than using a dynamically-generated IN clause:

-- Suppose @ID_List is a table with IDs for filter
SELECT E.* FROM Employees E
JOIN @ID_List IL ON E.EmployeeID = IL.ID; -- Let the matchmaking begin 👩‍❤️‍👨

Unpacking using table-valued params

Table-valued parameters (TVPs) provide a robust approach of passing list-like parameters to stored procedures without dynamic SQL:

-- First step: create a type
CREATE TYPE dbo.IDList AS TABLE(ID INT);

-- Next, use this type in your procedure
CREATE PROCEDURE FetchEmployees (@IDList dbo.IDList READONLY)
AS
BEGIN
    SELECT E.* FROM Employees E
    INNER JOIN @IDList IL ON E.EmployeeID = IL.ID; -- Time to meet the employees who got the beans 😄
END;
-- Now, you can pass a list of IDs directly