Why do we use WHERE 1=1 in the SQL Script?

A lot of people ask us why we use WHERE 1=1 in the SQL scripts. Here, we explain why we use WHERE 1=1 in the SQL Scripts. If you are a novice, you can also get knowledge of SQL Scripts.


The below script creates a customer table with sample records.


CREATE TABLE Customer
(
       ID int,
       [Name] VARCHAR(50),
       Loation VARCHAR(50)
)
GO
INSERT INTO Customer
SELECT 1,'Rajendra','India' UNION ALL
SELECT 2,'Manoj','USA' UNION ALL
SELECT 3,'Kashish','Jaipur'
Go

Suppose you write a dynamic SQL so that if the input value is null, we do not need to compare.


DECLARE @ID INT, @Location VARCHAR(50), @sql NVARCHAR(100)
SET @Location='India'
SET @sql = 'Select * from Customer where 1=1'
IF @Location<>''
       set @sql += ' and Location='''+@Location +''''
exec sp_executesql @sql 

Why do we use WHERE 1=1 in the SQL Script?

If you do not enter the parameter value, the query returns all rows because it does not include the location condition due to the false if condition.


DECLARE @ID INT, @Location VARCHAR(50), @sql NVARCHAR(100)
SET @Location=''
SET @sql = 'Select * from Customer where 1=1'
IF @Location<>''
       set @sql += ' and Location='''+@Location +''''
exec sp_executesql @sql

Dynamic SQL Script wirh WHERE 1=1

17 views0 comments