Converting Values to 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

SQL Server 2005 Forums

Transact-SQL (2005)

Converting Values to NULL

Author

Topic

JacquesThomas
Starting Member

6 Posts

Posted - 2007-06-30 : 20:11:47

Hi there everyone

I have a problem, i am developing an app in asp.net 2.0 and sql 2005. I need to search the database for products. Now the problem is that my fields are not all compulsory. On my search page i have a lot of dropdownlsts with the default text 'Please Select' or something similar. I need to convert the 'Please select' to NULL. Basically the opposite of the ISNULL() function i guess?
My query will be something like this:
select * from dbo.[Table] where (make = @make) AND (model = @model) and .....
In the where clause i need to test if the parameter make's value is equal to 'Please Select' and if so make it equal to NULL else pass the Value that it contains.

Thanks for any advice or help.

Regards
Jacques

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-30 : 20:15:57

nullif

KH
[spoiler]Time is always against us[/spoiler]

JacquesThomas
Starting Member

6 Posts

Posted - 2007-06-30 : 21:00:26

i am trying the nullif function. Can you maybe help me with it. as i can get it work

JacquesThomas
Starting Member

6 Posts

Posted - 2007-06-30 : 21:06:37

Maybe this will explain why i am having troubles.
i would normally query like this:
select * from table where ((@make IS NULL) OR (make = @make))
Now i need to convert the 'please select' value to NULL and then pass it to "@make IS NULL"
Can someone maybe help me with a sample statement or point me in the right direction

JacquesThomas
Starting Member

6 Posts

Posted - 2007-06-30 : 21:22:09

Solved it with:
SET @var1 = NULLIF(@cMake,'Please Select');
select * from table where ((@var1 IS NULL) OR (Make = @Make))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-02 : 10:34:18

Try this also
select * from table where ((@cMake ='Please Select') OR (Make = @Make))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources