Using ISNULL isn't working in SQL Server 2005

[andym0908]

Hi all

I have 2 tables. One called 'members' and the other called 'addresses'. Naturally there are members that are linked to addresses, it's quite simple.

I'm using a simple query to pull 2 fields from my members table which also should pull back around 6 fields from the addresses table. This works fine, but when there's not an address associated to a member, then obviously the query (below) returns NULLS.

Now, the problem is:- I'm using the SQL Function ISNULL to eliminate any NULL values, but it's just not working. What am I doing wrong?

The query:-

alter proc get_BizDetail
(
	@strID varchar(32)
)
as


	select biz_name,email,
	(select ISNULL(line1,' ') from addresses a where a.member_uid=m.member_uid) line1,
	(select ISNULL(line2,' ') from addresses a where a.member_uid=m.member_uid) line2,
	(select ISNULL(town,' ') from addresses a where a.member_uid=m.member_uid) town,
	(select ISNULL(county,' ') from addresses a where a.member_uid=m.member_uid) county,
	(select ISNULL(postcode,' ') from addresses a where a.member_uid=m.member_uid) postcode
	from members m where member_uid=@strID

go

 

[gmmastros]

Try this instead.

[COLOR=blue]alter[/color] [COLOR=blue]proc[/color] get_BizDetail
(
    @strID [COLOR=blue]varchar[/color](32)
)
[COLOR=blue]as[/color]


    [COLOR=blue]select[/color] biz_name,email,
           [COLOR=#FF00FF]ISNULL[/color](line1,[COLOR=red]' '[/color]) line1,
           [COLOR=#FF00FF]ISNULL[/color](line2,[COLOR=red]' '[/color]) line2,
           [COLOR=#FF00FF]ISNULL[/color](town,[COLOR=red]' '[/color]) town,
           [COLOR=#FF00FF]ISNULL[/color](county,[COLOR=red]' '[/color]) county,
           [COLOR=#FF00FF]ISNULL[/color](postcode,[COLOR=red]' '[/color]) postcode
    [COLOR=blue]from[/color]   members m 
           [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] addresses a
             [COLOR=blue]On[/color] m.member_uid = a.member_uid
    [COLOR=blue]where[/color]  m.member_uid=@strID

You only need to link to the address table once using a left join. If there is no match, then the columns from the addresses table will be null, and the IsNull function will accommodate it.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

that is because your queries do NOT return a resultset

for example run this

select isnull(name,'a') from sysobjects where id = -2

see? nothing

BTW instead of doing 5 nested queries use a left outer join between the two tables and ISNULL will work


read faq183-4785

because the way that query you posted is written is very inefficient

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[andym0908]

Thank you very much.. makes perfect sense!

 

[AlexCuse]

Now may be a good time to get into the habit of using COALESCE as well

If you go to Denis' blog I believe he has a good post on the differences.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[SQLDenis]

Yes, here:

http://sqlservercode.blogspot.com/2006/06/coalesce-and-isnull-differences.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions