Prev: Nesting Insert/EXEC
Next: How to get all constraints on all tables in a DB
From: tshad on 23 Mar 2010 14:33
I do this all the time but can't seem to see why this doesn't work

This gives me back 4 rows when @UserID is not null.

SELECT ChildGroupID = g.GroupID, ParentGroupID = NULL, Level=0
FROM UserGroup ug with (NOLOCK)
INNER JOIN Group] g with (NOLOCK) ON ug.GroupID = g.GroupID
WHERE (ug.UserID = @UserID AND g.Enabled = 1)

This one gives me back about 200 rows when @UserID is not null????

SELECT ChildGroupID = g.GroupID, ParentGroupID = NULL, Level=0
FROM UserGroup ug with (NOLOCK)
INNER JOIN Group] g with (NOLOCK) ON ug.GroupID = g.GroupID
WHERE @UserID IS NOT NULL OR (ug.UserID = @UserID AND g.Enabled = 1)

It should see the that the left side of the OR is False and do the right
side.

But now it comes up with way more rows?????

Since @UserID is NOT null, it is essentially the same statement, isn't it.

Thanks,

Tom


From: Eric Isaacs on 23 Mar 2010 14:46
If the @UserID is NOT NULL, then the left side is always true, so
you'll get all rows returned.

> WHERE @UserID IS NOT NULL OR ...


-Eric Isaacs
From: Michael MacGregor on 23 Mar 2010 15:48
No it is not the same thing, it's an OR statement, it will evaluate both
sides and return results accordingly, so by using

WHERE @UserID IS NOT NULL OR (ug.UserID = @UserID AND g.Enabled = 1)

you will get rows that satisfy BOTH conditions, hence the additional 196
rows.

Michael MacGregor


From: tshad on 23 Mar 2010 15:55
You're right and I should have seen it.

What it needed to be was:

WHERE @UserID IS NOT NULL AND (ug.UserID = @UserID AND g.Enabled = 1)

Thanks,

Tom

"Michael MacGregor" <nospam(a)nospam.com> wrote in message
news:Ob7TrlryKHA.5288(a)TK2MSFTNGP05.phx.gbl...
> No it is not the same thing, it's an OR statement, it will evaluate both
> sides and return results accordingly, so by using
>
> WHERE @UserID IS NOT NULL OR (ug.UserID = @UserID AND g.Enabled = 1)
>
> you will get rows that satisfy BOTH conditions, hence the additional 196
> rows.
>
> Michael MacGregor
>
>


 | 
Pages: 1
Prev: Nesting Insert/EXEC
Next: How to get all constraints on all tables in a DB