Deal with NULLs 4

[topub]

Hi everyone,

I have a table: T1
Col1 Col2
NULL 1
son 1
sun 3
NULL 4
su 5

And a stored Procedure with a query that queries that above table.

----- Stored Proc ----
Create procedure (@param1, @param2)
...
query
...
GO
---- End of Stored Proc ----


I need to write a query, as part of a Stored Procedure, to be able to accept NULL values as parameter (for col1) and still be able to return matching records.

Example: If I give @param1 = NULL and @param2 = 1 I want to get the first and second rows. But if @param1 = 'son' and @param2 = 1, I want the results to be only First record.

Query:
Version 1
select *
from T1
where col1 = COALECSE(@param1, col1)
and col2 = COALESCE(@param2, col2)

Comments:
For this, if I send parameters as @param1 = NULL, @param2 = 1. I don't get any records. Cuz' col1 has null value]

Version 2

check which parameters do not have nulls and accordingly build a SQL query. Then run it using EXEC()

Comments:
If there 10 parameters, this might get ugly

There must be a better way to do this....

Please suggest,
_ub

 

[PWise]

try
where isnull(col1,'') = case @pram1 when null then isnull(col1,'') else @pram1 end

 

[bborissov]

How about:

    [COLOR=blue]select[/color] *
     [COLOR=blue]from[/color] T1
     [COLOR=blue]where[/color] [COLOR=#FF00FF]COALESCE[/color](col1,[COLOR=red]''[/color]) = [COLOR=#FF00FF]COALESCE[/color](@param1, [COLOR=red]''[/color])
     and [COLOR=#FF00FF]COALESCE[/color](col2,[COLOR=red]''[/color]) = [COLOR=#FF00FF]COALESCE[/color](@param2, [COLOR=red]''[/color])

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

Why not this simple change:

select *
     from T1
     where (col1 = @param1 or col1 is null)
     and col2 = @param2

<.

 

[gmmastros]

Here:

[COLOR=blue]Declare[/color] @T1 [COLOR=blue]Table[/color](Col1 [COLOR=blue]VarChar[/color](10), Col2 [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @T1 [COLOR=blue]Values[/color](NULL ,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @T1 [COLOR=blue]Values[/color]([COLOR=red]'son'[/color],1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @T1 [COLOR=blue]Values[/color]([COLOR=red]'sun'[/color],3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @T1 [COLOR=blue]Values[/color](NULL ,4)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @T1 [COLOR=blue]Values[/color]([COLOR=red]'su'[/color] ,5)

[COLOR=blue]Declare[/color] @Col1Value [COLOR=blue]VarChar[/color](10)
[COLOR=blue]Declare[/color] @Col2Value [COLOR=blue]Int[/color]

[COLOR=blue]Set[/color] @Col1Value = NULL
[COLOR=blue]Set[/color] @Col2Value = 1

[COLOR=blue]Select[/color] 	* 
[COLOR=blue]From[/color] 	@T1
[COLOR=blue]Where[/color]	Col2 = @Col2Value
		And (@Col1Value [COLOR=blue]Is[/color] NULL Or Col1 = @Col1Value)


[COLOR=blue]Set[/color] @Col1Value = NULL
[COLOR=blue]Set[/color] @Col2Value = 1

[COLOR=blue]Select[/color] 	* 
[COLOR=blue]From[/color] 	@T1
[COLOR=blue]Where[/color]	Col2 = @Col2Value
		And (@Col1Value [COLOR=blue]Is[/color] NULL Or Col1 = @Col1Value)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I meant to have the last
Set @ColValue = 'son'

So that when you ran this... you would see that it does do what you want. This is essentially the same solution that monksnake wrote, the only difference is that I create a table variable for testing purposes.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Mine doesn't work.

<.

 

[monksnake]

Thanks for the *, for that, I will get it to work just so I will have done something to earn it.

<.

 

[PWise]

isnull(col1,'') = isnull(col1,'') and @pram1 is null
or col1= @pram1 and @pram1 is not null

 

[topub]

that was simple.

!!how could I not think of that!!

Thanks everyone.
_ub

 

[monksnake]

Ok, back from lunch, here's what I got:

select *
     from @t1
       where 
          (@param1 is null 
           (or col1 = @param1 and @param1 is not null)) 
           and 
           col2 = @param2

Looks very similar to pwise's.

<.