Get username or email and set 0 to the other

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get username or email and set 0 to the other

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-11-19 : 20:31:55

Hi.
I'm trying the following.
I want to find a username and email.
If the username does not exists then i want to set the return to zero (or null)the same with the email.Note that if a username or email exists then there will always be a matching email or username.
So i'm trying to see which one of the two existed and the row was fetched.
I know i can do a double select in an sp but i'm trying it with a single select that will do everything.
Here is what i have so far but it does not work(will bring empty rows)

declare @username nvarchar
set @username='testusername'
declare @email nvarchar
set @email='testemail@test.com'
select CASE WHEN doctors.username <> @username THEN '0' ELSE doctors.username END as username,
CASE WHEN doctors.email <> @email THEN '0' ELSE doctors.email END as email from doctors
where doctors.username=@username or doctors.email=@email

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-11-19 : 20:40:34

Hmm.Looks like i needed to declare the nvarchar size on a simple query to get it to work.
So it works ok now but any other suggestion for a better query is appreciated.

bobmcclellan
Starting Member

46 Posts

Posted - 2010-11-19 : 23:02:24

quote:
Originally posted by sapator

Hmm.Looks like i needed to declare the nvarchar size on a simple query to get it to work.
So it works ok now but any other suggestion for a better query is appreciated.

declare @username nvarchar(20)
set @username='testusername'
declare @email nvarchar(20)
set @email='testemail@test.com'

select CASE WHEN doctors.username <> @username THEN '0' ELSE doctors.username END as username,
CASE WHEN doctors.email <> @email THEN '0' ELSE doctors.email END as email

from doctors

where doctors.username=@username
or doctors.email=@email

Your code looks fine.
What are you trying to accomplish that this does not do?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-11-20 : 00:53:03

No the code works i was just asking if there is a better alternative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources