Have you ever come across the code snippet “WHERE 1=1” in SQL Server and wondered what it means? In this article, we will explore the purpose and usage of this seemingly redundant condition.

Typically, the code “WHERE 1=1” is used when dynamically constructing the WHERE clause based on input values. Let’s consider an example to better understand its usage.

Suppose we have a dataset called #t with columns emp_name, experience, and specialist. We want to use two parameters, @experience and @specialist, and pass values to them. However, if the values are null, we need to skip the comparison.

Here is an example code snippet:

DECLARE @experience INT, @specialist VARCHAR(100), @sql VARCHAR(100)
SET @sql = 'WHERE 1=1'
SET @sql = @sql + CASE WHEN @experience IS NULL THEN '' ELSE ' AND experience=''' + CAST(@experience AS VARCHAR(100)) + '''' END
SET @sql = @sql + CASE WHEN @specialist IS NULL THEN '' ELSE ' AND specialist=''' + CAST(@specialist AS VARCHAR(100)) + '''' END
SELECT @sql AS value

In the above code, we initialize the @sql variable with “WHERE 1=1”. This ensures that even if no values are passed for comparison, the WHERE clause will still be syntactically correct.

Let’s execute the code with no values passed:

DECLARE @experience INT, @specialist VARCHAR(100), @sql VARCHAR(100)
SET @sql = 'WHERE 1=1'
SELECT @sql AS value

The result will be:

value
---------------
WHERE 1=1

As expected, since no values are passed, the column value comparisons are skipped, and we have “WHERE 1=1” in the WHERE condition.

Now, let’s execute the code with a value passed for the @specialist parameter:

DECLARE @experience INT, @specialist VARCHAR(100), @sql VARCHAR(100)
SET @sql = 'WHERE 1=1'
SET @specialist = 'SQL'
SET @sql = @sql + CASE WHEN @experience IS NULL THEN '' ELSE ' AND experience=''' + CAST(@experience AS VARCHAR(100)) + '''' END
SET @sql = @sql + CASE WHEN @specialist IS NULL THEN '' ELSE ' AND specialist=''' + CAST(@specialist AS VARCHAR(100)) + '''' END
SELECT @sql AS value

The result will be:

value
-------------------------------
WHERE 1=1 AND specialist='SQL'

As you can see, since a value for the specialist column is passed, it is included in the WHERE clause.

To execute the statement, you can use the EXEC function:

EXEC('SELECT * FROM #t ' + @sql)

By using “WHERE 1=1” as the first condition, you can dynamically build the WHERE clause based on input values. This approach allows for flexibility and avoids syntax errors when no values are passed.

Next time you come across the code “WHERE 1=1” in SQL Server, you’ll know its purpose and how it can be used to construct dynamic WHERE clauses.