Where 1=1

[ST]

Hello,

I recently saw an article showing some coding which had a where clause of "Where 1=1". The article did not state what is was and I don't recall seeing that before. Any help?

ST

[Nikey]

I am a sql programmer and many time if I simply want to return the column names, I use

select * from <table_name> where 1 = 2. So it doesn&#39;t return any data but just the column name.

I haven&#39;t seen condition where 1 = 1 though..


------------
ST at 6/21/01 11:52:53 AM

Hello,

I recently saw an article showing some coding which had a where clause of &#34;Where 1=1&#34;. The article did not state what is was and I don&#39;t recall seeing that before. Any help?

ST

[JC Moore]

I use it for two purposes (normally):
1. Cursor loops
OPEN MyCursor
WHILE(1=1)
BEGIN
FETCH MyCursor INTO @MyValue
IF @@FETCH_STATAUS <> 0
BREAK

--DO SOMETHING
END
CLOSE MyCursor
DEALLOCATE MyCursor

2. I also use it to process records one at a time without using a cursor. This is very handy when you don&#39;t want to maintain a lock on the entire result set of the cursor for the duration it is open. It is also faster than a cursor.

Example:

Table MyTable has a primary key &#34;TableID&#34; and an INT field that is null &#34;TableData&#34;. Let&#39;s say you want to update all the fields from null to something that is not null, but you have to do them one at a time.

DECLARE @TableID INT,
@TableData INT

WHILE (1=1)
BEGIN

SET ROWCOUNT 1

SELECT @TableID = TableID
FROM MyTable
WHERE TableData IS NULL

IF @@ROWCOUNT = 0
BREAK

SET ROWCOUNT 0
-- calculate a value for @TableData for this row

UPDATE MyTable
SET TableData = @TableData
WHERE TableID = @TableID
END

SET ROWCOUNT 0


I hope this helped you.
-JC



------------
ST at 6/21/01 11:52:53 AM

Hello,

I recently saw an article showing some coding which had a where clause of &#34;Where 1=1&#34;. The article did not state what is was and I don&#39;t recall seeing that before. Any help?

ST

[Brad Allen]

Some database engines like Informix pop up a message box telling you the query will return a large recordset. To avoid this a true where clause is added to the select statement to get past the query engine.


------------
ST at 6/21/01 11:52:53 AM

Hello,

I recently saw an article showing some coding which had a where clause of &#34;Where 1=1&#34;. The article did not state what is was and I don&#39;t recall seeing that before. Any help?

ST

[Frank Pinkston]

I have also seen this used in building dynamic where clauses from user-input in a web based form. For example, if thwe user
searches on a state the where clause would be dynamically built as
WHERE 1=1 AND STATE = GA&#39;

If the user did not specity a state the where clause would be
WHERE 1=1





------------
ST at 6/21/01 11:52:53 AM

Hello,

I recently saw an article showing some coding which had a where clause of &#34;Where 1=1&#34;. The article did not state what is was and I don&#39;t recall seeing that before. Any help?

ST