Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

WHERE 1 = 1 Expand / Collapse
Author
Message
Posted Friday, August 13, 2010 3:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:47 AM
Points: 65, Visits: 259
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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:17 PM
Points: 2,035, Visits: 3,465
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502
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=1
begin

<Do something>

if <some condition is true> break

<Do something else>

if <some other condition is true> break

end

Post #969286
Posted Friday, August 13, 2010 3:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:17 PM
Points: 2,035, Visits: 3,465
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=1
begin

<Do something>

if <some condition is true> break

<Do something else>

if <some other condition is true> break

end


Actually, I like this explanation better!
Post #969288
Posted Friday, August 13, 2010 3:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:47 AM
Points: 65, Visits: 259
Thanks to both of you for your quick replies!
Post #969297
Posted Friday, August 13, 2010 3:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,212, 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 = NULL
AS
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
RETURN
END

Post #969301
Posted Friday, August 13, 2010 4:11 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 598, Visits: 3,816
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 = NULL
AS
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
RETURN
END



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 #tmp
from #AnotherTable
where 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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:01 AM
Points: 177, Visits: 357
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

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:37 AM
Points: 4,783, Visits: 208
I have seen this too in the USP's where 1 = 1???
Post #971388
Posted Tuesday, April 3, 2012 11:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse