March 13, 2007 at 5:11 am
Hello.
I´m trying to retrieve a name (just for test) based on input parameters. In case the input doesent match any columns in the table i use an ISNULL.
Im currently using SQL Server 2005 Express and the column containing the password is of type nvarchar(20).
For some reason i just cant get it to work. What im i missing? Im sure its really simple but sometimes you just cant figure it out.
This is the statement.:
SELECT
ISNULL(Password,'NoUser') as Result
FROM [User]
WHERE TSICID = 'TEST02'
'TEST02' doesent exist in the table and i would like to get a default value instead. But the ISNULL doesent seem to be working. I just seems to return nothing or maybe an empty string.
Im pretty sure this is the way i used to do it with SQL Server 2000 and it worked.
All help i much appreciated.
Thanks.
/V
March 13, 2007 at 5:31 am
That would not work in 2000 either - if there is no entry for TEST02 in the table then there will be no row returned - if there is a row for TEST02 but the password is null then you will return 'NoUser'
If you want to test to see if TEST02 exists in the table then
if not exists (select 1 from [User] where TSICId = 'TEST02')
select 'NoUser'
If you are trying to join from a table where TEST02 does exist then
something like this would work
set nocount on
declare @table table(UserId int ,email varchar(20))
declare @table2 table (UserId int)
insert into @table2
select 1
union
select 2
union
select 3
insert into @table
select 1,'a@abc.com'
union
select 2,'b@abc.com'
select a.UserId,isnull(b.email,'NoUser') as Email
from @table2 a
left join @table b on a.userId = b.UserId
where a.userid = 3
hope this helps
s
March 13, 2007 at 6:12 am
Thanks a lot!
Dont really know how i was thinking.
This helps.
March 14, 2007 at 7:48 am
Very simple.
SELECT distinct (case when count(Pswrd)=0 then 'NoUser' end) as Result
FROM WHERE last = 'TEST02'
March 15, 2007 at 9:00 am
I don't think Mark's query returns the password if the TEST02 user actually exists. Perhaps this will work.
select top 1 password as Result
from (
select 1 as SortOrder, last, password
from [dbo].
where last = 'TEST02'
union all
select 2 as SortOrder, 'NoUser' as last, 'NoUser' as password
) as users
order by SortOrder
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply