[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1379
  • Last Modified:

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.
0
juststeve
Asked:
juststeve
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now