Solved

IF EXISTS and multiple WHERE conditions

Posted on 2003-11-16
6
1,368 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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:ScottPletcher
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

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 …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now