Function to eliminate null result set

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 2000 Forums

SQL Server Development (2000)

Function to eliminate null result set

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-24 : 16:38:32

Guys,

I have a scenario where I need to display the column values through a function if only they are populated.

For eg: in the below query

select empname, deptname, coursename, experience from employee where empid = 21

if deptname is null then it should not be shown in the result set
or if coursename is null or '' it should not be shown in the result set

Any suggestions how to write a function to print this result set

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-24 : 16:41:47

quote:
I have a scenario where I need to display the column values through a function if only they are populated.

Could you explain the reasoning, and give an example of the source data and expected output?

SELECT statements cannot return a dynamic number of columns, you'd have to write multiple SELECTs to eliminate the columns you don't want.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-24 : 16:56:54

empname deptname coursename exp
_______________________________
John Physics Atoms
Smith Anatomy 2

so basically my query

is

select 'empname:' empname + 'deptname:' deptname + 'coursename:' coursename + 'exp:' exp
from employee

so in the case of null or missing columns I should be able to eliminate those columns since query result is a concatenated string.

I am trying to cursor for this purpose any suggestions woould be helpful indeed.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-24 : 17:29:08

Well if you're concatenating to a single column then how about something like:


--this only works if missing data values are NULL rather than an empty string.
--if there are empty strings you'll need to use NullIf 
--or CASE with checks for LEN(<col>) or something.

select isNull('empname: ' + empname, '') +
       isNull('deptname: ' + deptname, '') +
       isnull('coursename: ' + coursename, '') +
       isnull('exp: ' + exp, '') as [OneLongColumn]
from   employee

Be One with the Optimizer
TG

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-24 : 22:03:13

TG,
This works fine for me, now trying to incorporate condition which not only looks for nulls but also '' (blanks).

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-25 : 07:33:43

try this: (this is what I was suggesting in my previous post)

select
case when nullif(empname,'') is null then '' else 'empname: ' + empname + ' ' end +
case when nullif(deptname,'') is null then '' else 'deptname: ' + deptname + ' ' end +
...
from ...

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-10-25 : 08:20:40

"case when nullif(empname,'') is null then '' else 'empname: ' + empname + ' ' end "

is equivalent to


COALESCE('empname: ' + NullIf(empname, '') + ' ', '')

but I dunno if its more efficient - but I think it should be

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources