IF EXISTS and multiple WHERE conditions

if exists (select strFName from mUsers where
  LOWER(strEmail) = LOWER(@strEmail) AND
  LOWER(strFName) = LOWER(@strFName) AND
  LOWER(strLName) = LOWER(@strLName)
)

I'm trying to determine if a record already exists for a user where all three fields match _against a single user record_. The above clause appears to return true regardless of how many records it takes to meet each condition.

I've tried variations such as:

DECLARE @tmp int
--check if name/email is on record
set @tmp = COUNT(select strFName from mUsers where
  LOWER(strEmail) = LOWER(@strEmail) AND
  LOWER(strFName) = LOWER(@strFName) AND
  LOWER(strLName) = LOWER(@strLName)
)
if @tmp = 1

but i can't get the syntax correct.
juststeveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

namasi_navaretnamCommented:
Try this,

DECLARE @tmp int

set @tmp = select count(*) from mUsers
where
  LOWER(strEmail) = LOWER(@strEmail)
  LOWER(strFName) = LOWER(@strFName)
  LOWER(strLName) = LOWER(@strLName)

if @tmp = 1
Begin
  -- Your Code
End
Else
Begin
  -- Your Code
End
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
You should not have to use the Lower function as T-SQL is case insensitive by default. namasi_navaretnam has it nearly right, except for the missing And operator and the paranthesis, so I would write it as follows:

Set @tmp = select count(*)
                  from mUsers
                  where strEmail = @strEmail
                            And strFName = @strFName
                            And strLName = @strLName

Or as:
Select @tmp = count(*)
from mUsers
where strEmail = @strEmail
          And strFName = @strFName
          And strLName = @strLName

Anthony
0
Anthony PerkinsCommented:
And it would help if I had included the paranthesis <g>:

Set @tmp = (select count(*)
                  from mUsers
                  where strEmail = @strEmail
                            And strFName = @strFName
                            And strLName = @strLName)

Anthony
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mironCommented:
Hi juststeve,

having just email address sould be  accurate to find record for a person.
--
if exists( select 1 from mUsers group by strEmail having strEmail = @strEmail AND count(*) > 1 )

--cheers
0
Scott PletcherSenior DBACommented:
I know this has already been graded, but I think you've got some other issue going on here; the check you coded should work fine and should only match "_against a single user record_".  
Perhaps you had multiple statements after the IF but didn't put a BEGIN, so everything past the first statement ran regardless of the result of the IF?

if exists (select strFName from mUsers where
  LOWER(strEmail) = LOWER(@strEmail) AND
  LOWER(strFName) = LOWER(@strFName) AND
  LOWER(strLName) = LOWER(@strLName)
)
BEGIN
    ...statement 1...
    ...statement 2...
    ...statement 3...
END

Without the BEGIN/END, statements 2 and 3 would run regardless of whether the IF were true or not.
0
juststeveAuthor Commented:
hmmm....I have the BEGIN/END in place. This is a fairly complex sproc that needs to handle a number of different conditions depending on what's passed in.

It seems to be working correctly ... it wasn't working until i implemented  the 'count' on the above clause.

sigh

it's probably complex enough and certianly important enough to have you folks review the full code. ... i'll post to a seperate thread and, for archival sake,  follow up here with any additional information as needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.