T-SQL Where 1=1 and Where 1 = 0 , what is the puprose of this statement ?

Question

Hi friends,

I am refering some already developed Reports Store Procedures by other experienced developers in SQL Server.

Most of the Store Proc contains Where 1 = 1 in the Select statement's Where Caluse very first and then it contains rest of the conditions in remaining Where Clauses.

My question, Why they are using Where 1 = 1 which is always True ?

Is there any performance gain with this ?

some time, I have also seen Where 1 = 0 in T-SQL queries.

can someone explain what is the pupose behind this ?

thanks.

 

Answer 1

It's possible they put it there when they were needing to turn off certain sql statements, perhaps during a debugging session.  That's the only thing I can think of.  If they put 1=0 it would cause the query to never return any matches.  Somebody might have done this when they were trying to isolate the cause of a report not working or something like that.  They must have just left the hooks in there.

---

Tom Overton

Answer 2

I've seen WHERE 1= 1 sometimes used to faciliate building dynamic SQL ov zero or more search conditions.  For example:

 

SET @SelectStatement = 'SELECT SomeData FROM dbo.SomeTable WHERE 1  = 1';
IF @Column1 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column1 = @Column1';
IF @Column2 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column2 = @Column2';

I sometimes wee WHERE 1 = 0 (or any always false condition) used to create a table of identical structure, sans data:

SELECT * INTO dbo.NewTable FROM dbo.SomeTable WHERE 0 = 1;

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

Lot of programmers use WHERE 1=1 in dynamic SQL statement,instead of WHERE
only they start with WHERE 1=1 then rest of the conditions.

They include this condition so that any run time conditions can be added with an And without doing any conditional check.

---

Shatrughna.