if not null - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

if not null

Author

Topic

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-30 : 15:42:21

Hi,

i have a query that i need.

lets say you have a table with a person_id, person_name and age

the query needs to allow you to get the person_id based on the name and age.

so select person_id where name = ? and age = ?

The problem that i have is that i need something to handle this even if an age was not supplied.... so if the parameter passed for age is null then you would still run the query and get all of the person_id's just based on the name

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-30 : 15:44:59

[code]select person_id
from table
where name = @name
and (age = @age or @age is null)[/code]

EDIT: This should be a good read.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-30 : 16:05:24

someone told me to use the coalesce function somehow... any ideas?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-30 : 16:06:35

I'm afraid not...unless that 'someone' was me.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-30 : 16:11:21

If you want to use COALESCE you can change:

and (age = @age or @age is null)

and age = COALESCE(@age, age)

Here is a link to an article about catch all queries that might be of interest:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-30 : 16:22:19

and age = COALESCE(@age, age) will not work because it will only show the non-null values of age.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-30 : 16:32:36

I didn't realize people have NULL ages. If that is true then you'd, probably, need to change the ANSI_NULLS setting in order to use COALESCE like that. Otherwise you need to expand the use of COALESCE To something like:

and COALESCE(age, -1) = COALESCE(@age, age, -1)

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-30 : 16:33:01

Sorry, i should have been more clear

if you have:
and age = COALESCE(@age, age)

and user does not supply an age you will have

and age = COALESCE(NULL, age)

and this will return all the rows where age is not null

this is incorrect, any suggestions?

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-07-30 : 16:35:33

ok Lamprey that works

thanks

harrisunderwork
Starting Member

4 Posts

Posted - 2010-08-01 : 01:16:04

Try something like this

SELECT * FROM table
where name =
case when ISNULL(@name, '') <> '' THEN @name else name end
and
age =
case when isnull(@age, '') <> '' THEN @age else age end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources