Solved

IF EXISTS and multiple WHERE conditions

Posted on 2003-11-16
6
1,370 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

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.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

792 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