Query with CASE and NULL values

Hi, please, take a look to this query:

declare @.IDCliente int
declare @.Cliente varchar(50)
declare @.IDUsuario int
declare @.IDUsuarioAlta int

set @.IDcliente = 0
set @.Cliente = ''
set @.IDUsuario = 0
set @.IDUsuarioAlta = 0

select * from cliente
where
(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)
AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)
AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END
AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

Cliente
IDCliente Cliente IDUsusario IDUsuarioAlta
1 Esteban 1 2
2 Jose 3 1
3 Mario 2 NULL
4 Pedro NULL 2
5 NULL 1 2

Its work fine, except for the NULL values. What can I do to fix it ?

thanks

Here you go:

Code Snippet

select IDCliente, Cliente,

case when IDUsusario is null then '' --or whatever you want in place of null

else IDUsusario

end as IDUsusario,

case when IDUsuarioAlta is null then '' -- same thing here

else IDUsuarioAlta

end as IDUsuarioAlta

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario ELSE @.IDUsuario END or idusuario is null)

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta ELSE @.IDUsuarioAlta END or idusuarioalta is null)

AND idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

I'm sorry, I think I didn't explain my self.

The problem is in the WHERE part, No in the SELECT.

When @.IDUsuario has a value, then the query return the record with the NULL value, and that is not correct. If I take off or idusuario is null

then the record with de NULL value is never return.

thanks and sorry my english !.

|||

AH, gotcha.

How about this then:

Code Snippet

select *

from cliente

where

(IDUsuario = CASE @.IDUsuario WHEN 0 THEN IDUsuario

ELSE @.IDUsuario

END

or (idusuario is null and @.IDUsuario = 0) )

AND (IDUsuarioAlta = CASE @.IDUsuarioAlta WHEN 0 THEN IDUsuarioAlta

ELSE @.IDUsuarioAlta

END

or (idusuarioalta is null and @.IDUsuarioAlta = 0) )

AND (idCliente = CASE @.idCliente WHEN 0 THEN idCliente ELSE @.idCliente END

or (idCliente is null and @.idCliente = 0) )

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||

This might be a little cleaner:

Code Snippet

select *

from cliente

where

((@.IDUsuario <> 0 and idusuario = @.IDUsuario ) or

(@.IDUsuario = 0))

AND ((@.IDUsuarioAlta <> 0 and IDUsuarioAlta = @.IDUsuarioAlta ) or

(@.IDUsuarioAlta = 0))

AND ((@.idCliente <> 0 and idCliente = @.idCliente ) or

(@.idCliente = 0))

AND Cliente LIKE '%' + CASE @.Cliente WHEN '' THEN Cliente ELSE @.Cliente END + '%'

|||*** !, it work perfect, i think you know this !! like we say in Argentina: "Sos Groso !!"..... is like say You are Big !... I think so..sql