Solved

IF EXISTS and multiple WHERE conditions

Posted on 2003-11-16
6
1,373 Views
Last Modified: 2012-08-14
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
Comment
Question by:juststeve
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 250 total points
ID: 9758967
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9759909
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9759912
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 9

Expert Comment

by:miron
ID: 9760175
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9763678
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
 

Author Comment

by:juststeve
ID: 9763769
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question