find 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

find null

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-11 : 21:43:25

hi all,

how do we include null record when we send param ''?

select * from tblA where @param=''

specs : when @param='whateva' find whateva, but when @param='' find all InCLudiNg NULL...

thanks

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-11 : 22:16:39

[code]
SELECT * FROM tblA WHERE (col = @val OR @val IS NULL)[/code]

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-11 : 22:22:44

but then doenst it will find 'whateva' and <NULL>

i want it to search only 'whateva' if @param='whateva'
but find evthing if @param=''

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 23:08:40

Not sure if this works

SELECT * FROM tblA WHERE (col = @val OR ISNULL(@val,'')='')

Madhivanan

Failing to plan is Planning to fail

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-11 : 23:54:32

did you try it? Set the value of the @val to 'whateva' and check it out.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-12 : 00:48:45

waa madhi.. it worked.. could u explain what's with ='') part?

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-12 : 00:56:24

quote:
Originally posted by dinakar

did you try it? Set the value of the @val to 'whateva' and check it out.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

PS- see who's worked :-


declare @param varchar(10)
set @param=''

-- Prepare sample data
DECLARE	@tbltest TABLE (ID varchar(12), Qty MONEY,  BB char(1) )

INSERT	@tbltest
SELECT	'A', 18,  null UNION ALL
SELECT	'B', 17,  null UNION ALL
SELECT	'C', 5,   'Y'UNION ALL
SELECT	'B1', 17,  'Y' 

--dinakar's
select * from @tbltest where (BB  = @param OR @param IS NULL)

--madhivanan's
select * from @tbltest where(BB = @param OR ISNULL(@param,'')='')

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-12 : 01:11:15

quote:
Originally posted by maya_zakry

quote:
Originally posted by dinakar

did you try it? Set the value of the @val to 'whateva' and check it out.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

PS- see who's worked :-

declare @param varchar(10)
set @param=''

-- Prepare sample data
DECLARE @tbltest TABLE (ID varchar(12), Qty MONEY, BB char(1) )

INSERT @tbltest
SELECT 'A', 18, null UNION ALL
SELECT 'B', 17, null UNION ALL
SELECT 'C', 5, 'Y'UNION ALL
SELECT 'B1', 17, 'Y'

--dinakar's
select * from @tbltest where (BB = @param OR @param IS NULL)

--madhivanan's
select * from @tbltest where(BB = @param OR ISNULL(@param,'')='')

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

kool. You can give the prize to madhi..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-12 : 01:13:47

To explain, the code works if (1) BB = @param or (2) ISNULL(@param,'') will check if the value is NULL, then assign it to '' and compare with ''.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-12 : 02:03:35

quote:
Originally posted by maya_zakry

waa madhi.. it worked.. could u explain what's with ='') part?

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~


If col=@val
     Select * from table where col=@val
else if @val is NULL or @val=''
     Select * from table

Madhivanan

Failing to plan is Planning to fail

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-12 : 06:05:39

thanks guys :)

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-12 : 06:11:36

oppss.. guys.. how do we eliminate null when we want to search @param='Y' ?? i expect when :-

1.@param='Y' find only Y EXCLUDing null (this dont work)
2.@param='' find ALL including null (this work)

thanks

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-12 : 06:53:33

What query you are using?? if you are using the Madhi's Query then it should work fine for you??


declare @param varchar(10)
set @param='Y'

-- Prepare sample data
DECLARE	@tbltest TABLE (ID varchar(12), Qty MONEY,  BB char(1) )

INSERT	@tbltest
SELECT	'A', 18,  null UNION ALL
SELECT	'B', 17,  null UNION ALL
SELECT	'C', 5,   'Y'UNION ALL
SELECT	'B1', 17,  'Y' 


select * from @tbltest where(BB = @param OR ISNULL(@param,'')='')

--Output


ID           Qty                   BB
------------ --------------------- ----
C            5.00                  Y
B1           17.00                 Y

(2 row(s) affected)

Chirag

http://chirikworld.blogspot.com/

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-12 : 06:59:34

phew.. yup yup.. it workde.. my mistake.. i left the isnull during 2nd time testing... phewww
thanks chirag

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-12 : 07:01:48

quote:
Originally posted by maya_zakry

phew.. yup yup.. it workde.. my mistake.. i left the isnull during 2nd time testing... phewww
thanks chirag

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Its happens

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-12 : 08:27:36

quote:
Originally posted by maya_zakry

phew.. yup yup.. it workde.. my mistake.. i left the isnull during 2nd time testing... phewww
thanks chirag

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

It seems you focus on problem, not solution

Now I rewrite your signature

Focus on Solution, not problem

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-12 : 08:41:41

quote:
Focus on Solution, not problem

That is funny ..!!

Chirag

http://chirikworld.blogspot.com/

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-12 : 08:45:32

you... NoTi geek

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-14 : 02:44:26

hi,

back again.. how do i apply this to find some range. Eg: given @param='' and @param='', how do i find all including null using BETWEEN?


declare @param varchar(10), @param2 varchar(10)
set @param=''
set @param2=''
-- Prepare sample data
DECLARE	@tbltest TABLE (ID varchar(12), Qty MONEY,  BB char(1) )

INSERT	@tbltest
SELECT	'A', 18,  null UNION ALL
SELECT	'B', 17,  null UNION ALL
SELECT	'C', 5,   'y' UNION ALL
SELECT	'B1', 17,  'z'  

select * from @tbltest where
BB >= coalesce(nullif(@param, ''),BB) and
BB<= coalesce(nullif(@param2, ''),BB)

how do i find :-
1.all Including Null when @param='' and @param1=''
2.all between x and y when @param='x' and @param1='y'
3.all from 'X' (including null) when @param='x' and @param1=''
4.all from 'Z' (including null) when @param='' and @param1='z'

so far only no.2 worked..
thankss//

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-14 : 02:46:29

please prioritize no.1
if no3 and no4 is imposibble that is OK

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-14 : 02:59:24

select * from @tbltest where
isnull(@param, '')='' and
isnull(@param2, '')=''

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources