April 6, 2012 at 3:28 pm
Just a little confused with OR operator in SQL Server.
I have the following query,the below query returns all rows from the table Item.
There exists an Item with ItemId=16
When I use 1=0 then it only returns Item with ItemId=16.
DECLARE @itemId INT
SET @itemId = 16
SELECT *
FROM Item i
WHERE ( i.ItemId = @itemId
OR 1 = 1
)
Regards
April 6, 2012 at 3:34 pm
Your OR operator is evaluated against each row.
Assume your @ItemID 16 is present in 3rd row of your table ( table contains only 5 rows, lets assume)
ItemID
22
23
16
42
52
So when the query engine touches the first row, it checks if itemID = 16 OR 1 = 1 . The first condtion fails as firstrow's itemid is 22 , so 22 != 16 , but as u r using OR clause with 1 = 1, it returns true for that row.
So your condition for the row becomes like this 22 = 16 or 1 = 1. If one of the either condition passes, that row is returned!
Hope this clarifies.. As a side note, what do u really want to acheive out of your query?
April 6, 2012 at 3:37 pm
I'm not sure why you're using the 1 = 1, but here's how I see it. When you use 1 = 0, that never evaluates to true for any rows, so the only row returned is for the correct item ID. When you use 1 = 1, that evaluates to true for all rows so all rows are returned.
April 6, 2012 at 3:59 pm
Thanks Chuck Hottle and ColdCoffee for your quick response
I am debugging a Stored Procedure and came across this SQL Statement.
The actual sql statement is some what like:
DECLARE @itemId INT
SET @itemId = 16
SELECT *
FROM Item i
WHERE
( i.ItemId = @itemId
OR 1 = CASE WHEN @IsProducedItems <> 1 THEN 1
ELSE 0
END
)
April 6, 2012 at 4:09 pm
jerry209 (4/6/2012)
Thanks Chuck Hottle and ColdCoffee for your quick responseI am debugging a Stored Procedure and came across this SQL Statement.
The actual sql statement is some what like:
DECLARE @itemId INT
SET @itemId = 16
SELECT *
FROM Item i
WHERE
( i.ItemId = @itemId
OR 1 = CASE WHEN @IsProducedItems <> 1 THEN 1
ELSE 0
END
)
the case statement is checking that the variable @IsProducedItems is not equal to 1 and if it is not display the record even if the @itemId does not equal i.ItemId
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply