Query with CASE and NULL values

Codigo47

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

DaleJ

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 + '%'

Codigo47

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 !.

DaleJ

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 + '%'

DaleJ

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 + '%'

Codigo47

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

Query with CASE and NULL values

Re: Query with CASE and NULL values

Re: Query with CASE and NULL values

Re: Query with CASE and NULL values

Re: Query with CASE and NULL values

Re: Query with CASE and NULL values