Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 WHERE 1 = 1 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, August 13, 2010 3:05 PM
 Valued Member Group: General Forum Members Last Login: Friday, September 20, 2013 11:45 AM Points: 63, Visits: 255
 I am afraid I am really going to show my newbie-ness with this question …I see lots of stored procedures with ‘WHERE 1 = 1’ in them and I’m wondering what the purpose of this is. 1 will always = 1 so I am confused why this is used. Will someone please explain … in newbie language Thanks!!
Post #969283
 Posted Friday, August 13, 2010 3:13 PM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 10, 2014 3:12 PM Points: 2,132, Visits: 3,399
 Actually, your question is not as bad as you think it is! As far as I know, this is used for testing . . . the clause "where 1 = 1" is generally a placeholder for what will eventually be a conditional statement. "where 1=1" will always eval to "true," where "where 1 = 2" will always eval to "false."
Post #969285
 Posted Friday, August 13, 2010 3:14 PM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 1:11 PM Points: 3,081, Visits: 11,230
 It is a way to create an endless loop that you will exit using the BREAK command, instead of when the test condition is true.`while 1=1begin if break if breakend`
Post #969286
 Posted Friday, August 13, 2010 3:16 PM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 10, 2014 3:12 PM Points: 2,132, Visits: 3,399
 Michael Valentine Jones (8/13/2010)It is a way to create an endless loop that you will exit using the BREAK command, instead of when the test condition is true.`while 1=1begin if break if breakend`Actually, I like this explanation better!
Post #969288
 Posted Friday, August 13, 2010 3:37 PM
 Valued Member Group: General Forum Members Last Login: Friday, September 20, 2013 11:45 AM Points: 63, Visits: 255
 Thanks to both of you for your quick replies!
Post #969297
 Posted Friday, August 13, 2010 3:48 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, October 24, 2012 2:12 PM Points: 1,213, Visits: 3,232
 "WHERE (1 = 1)" is sometimes convenient when generating dynamic SQL. It avoids having to determine if a particular filter criterion in the WHERE clause is the first in the list, so all filter criteria can be prefixed with "AND". For example:`CREATE PROCEDURE MyDynamicQuery @Param1 varchar(20) = NULL, @Param2 varchar(50) = NULL, @Param3 datetime = NULLAS BEGIN DECLARE @sql nvarchar(1000) DECLARE @paramList nvarchar(1000) SELECT @paramList = N'@P1 varchar(20), @P2 varchar(50), @P3 datetime' SELECT @sql = N'SELECT Col1, Col2, Col3 FROM dbo.MyTable WHERE (1=1)' IF NOT (@Param1 IS NULL) SELECT @sql = @sql + N' AND (Col1 = @P1)' IF NOT (@Param2 IS NULL) BEGIN SELECT @Param2 = @Param2 + '%' SELECT @sql = @sql + N' AND (Col2 LIKE @P2)' END IF NOT (@Param3 IS NULL) SELECT @sql = @sql + N' AND (Col3 >= @P3)' --PRINT @sql EXEC sp_executesql @sql, @paramList, @Param1, @Param2, @Param3 RETURNEND`
Post #969301
 Posted Friday, August 13, 2010 4:11 PM
 Right there with Babe Group: General Forum Members Last Login: Tuesday, February 25, 2014 8:59 AM Points: 754, Visits: 3,813
 andrewd.smith (8/13/2010)"WHERE (1 = 1)" is sometimes convenient when generating dynamic SQL. It avoids having to determine if a particular filter criterion in the WHERE clause is the first in the list, so all filter criteria can be prefixed with "AND". For example:`CREATE PROCEDURE MyDynamicQuery @Param1 varchar(20) = NULL, @Param2 varchar(50) = NULL, @Param3 datetime = NULLAS BEGIN DECLARE @sql nvarchar(1000) DECLARE @paramList nvarchar(1000) SELECT @paramList = N'@P1 varchar(20), @P2 varchar(50), @P3 datetime' SELECT @sql = N'SELECT Col1, Col2, Col3 FROM dbo.MyTable WHERE (1=1)' IF NOT (@Param1 IS NULL) SELECT @sql = @sql + N' AND (Col1 = @P1)' IF NOT (@Param2 IS NULL) BEGIN SELECT @Param2 = @Param2 + '%' SELECT @sql = @sql + N' AND (Col2 LIKE @P2)' END IF NOT (@Param3 IS NULL) SELECT @sql = @sql + N' AND (Col3 >= @P3)' --PRINT @sql EXEC sp_executesql @sql, @paramList, @Param1, @Param2, @Param3 RETURNEND`Interesting! Ray, you're right too.As a side note, I've seen people use WHERE 1 = 0 used too for creating a temp table with no records (i.e., copying the structure into an empty temp table)select *into #tmpfrom #AnotherTablewhere 1=0 ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #969304
 Posted Tuesday, August 17, 2010 11:24 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, November 21, 2013 8:35 AM Points: 177, Visits: 355
 I see that a lot with dynamic SQL I used to search my reversed dynamic SQL for erehw and remove it if it started the trimmed string, but 1=1 works just as well.
Post #970629
 Posted Wednesday, August 18, 2010 12:54 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Saturday, March 24, 2012 3:31 PM Points: 4,587, Visits: 207
 I have seen this too in the USP's where 1 = 1???
Post #971388
 Posted Tuesday, April 03, 2012 11:06 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, May 16, 2012 2:36 PM Points: 9, Visits: 6
 There is a simple logic behind putting where 1=1 while creating the dynamic queries.For ex: for getting user information based on the few conditions like with Firstname or Lastname or both.SqlQuery= "Select * from tblUser Where 1=1 ";If(firstname is not null)SqlQuery=SqlQuery & " And Firstname="+@Firstname ; If(Lastname is not null)SqlQuery=SqlQuery & " And Lastname ="+@Lastname ;Explanation:If we have not mentioned Where 1=1 in above query there could be a more logical code we have to write for checking for adding the AND in front of Firstname and Lastname. So for avoiding that logical code and performance point many are using "WHERE 1=1" in the dynamic queries.Please let me know it helps you are not.
Post #1277488

 Permissions