Array
(
)

Case When dentro do Where

Anilgome
   - 17 jun 2008

Bom Dia a Todos,

Estou tentando fazer um select simples, dentro de uma stored procedure, e na condição dessa minha stored procedure eu tenho que fazer o seguinte:

1- Se foi passado um código como parametro filtrar por esse código.
2- Se não foi passado nenhum código ( 0 ou Null ), então devo retornar todos os registros.

Consegui resolver o problema da seguinte maneira...

#Código
CREATE PROCEDURE TESTE

-- CÓDIGO DA PROPOSTA
@CD_CONSULTA INTEGER,
@NM_FANTASIA_CLIENTE VARCHAR(30)

AS

SELECT
*

FROM
CONSULTA_ITENS CIT

WHERE
CIT.CD_CONSULTA = ( CASE WHEN ( ISNULL( @CD_CONSULTA , 0 ) = 0 ) THEN CIT.CD_CONSULTA ELSE @CD_CONSULTA END )
CIT.NM_FANTASIA_CLIENTE = ( CASE WHEN ( ISNULL( @NM_FANTASIA_CLIENTE, ´´ ) = ´´ ) THEN CIT.NM_FANTASIA_CLIENTE ELSE @NM_FANTASIA_CLIENTE END )

GO


Dessa maneira, o select funciona e filtra corretamente de acordo com a minha necessiadade, porém o número de reads, e o tempo da procedure estão muito altos. Tentei de uma segunda maneira que diminuiu consideravelmente os reads, e o tempo de resposta, mas é uma forma que eu não acho que seja ideal trabalhar, seria criar um SQL dinamico com uma string, verificar se passou algum parametro e somente se sim concratenar o ´where´ no select. Fiz apenas por teste e dessa maneira ele fez apenas 20¬ da quantidade de reads do que com o código acima.

Gostaria de saber se alguem pode me indicar uma forma de resolver esse problema.

Muito obrigado pela atenção.

Emerson
   - 17 jun 2008

me parece bem simples. basta tirar o case:
#CódigoCREATE PROCEDURE TESTE

-- CÓDIGO DA PROPOSTA
@CD_CONSULTA INTEGER,
@NM_FANTASIA_CLIENTE VARCHAR(30)

AS
SELECT
*
FROM
CONSULTA_ITENS CIT
WHERE
CIT.CD_CONSULTA = ISNULL(@CD_CONSULTA, CIT.CD_CONSULTA)
CIT.NM_FANTASIA_CLIENTE = ISNULL(@NM_FANTASIA_CLIENTE, CIT.NM_FANTASIA_CLIENTE)


Emerson
   - 17 jun 2008

me parece bem simples. basta tirar o case:#CódigoCREATE PROCEDURE TESTE

-- CÓDIGO DA PROPOSTA
@CD_CONSULTA INTEGER,
@NM_FANTASIA_CLIENTE VARCHAR(30)

AS
SELECT
*
FROM
CONSULTA_ITENS CIT
WHERE
CIT.CD_CONSULTA = ISNULL(@CD_CONSULTA, CIT.CD_CONSULTA) and
CIT.NM_FANTASIA_CLIENTE = ISNULL(@NM_FANTASIA_CLIENTE, CIT.NM_FANTASIA_CLIENTE)


ou ainda:#Código
CREATE PROCEDURE TESTE
-- CÓDIGO DA PROPOSTA
@CD_CONSULTA INTEGER,
@NM_FANTASIA_CLIENTE VARCHAR(30)

AS
SELECT
*
FROM
CONSULTA_ITENS CIT
WHERE
(@CD_CONSULTA is null or CIT.CD_CONSULTA = @CD_CONSULTA) and
(@NM_FANTASIA_CLIENTE is null or CIT.NM_FANTASIA_CLIENTE = @NM_FANTASIA_CLIENTE)


Anilgome
   - 17 jun 2008

Muito obrigado pela resposta, agradeço a atenção.

Testei aqui e também funciona da sua forma, porém, quando fui analisar o plano de execução, ele está dando um table scan na tabela, mesmo quando passado um código de consulta.

No caso de uma consulta dinamica, onde eu concrateno o SQL em string, e faço algo assim.

#Código
declare
@sql varchar(2000)

set @sql = ´select ...´

if ( @cd_consulta > 0 )
@sql = @sql + ´where cd_consulta = @cd_consulta´

exec( @sql )


Algo basicamente assim, quando é executado passando o código da consulta, o SQL entende que tem que utilziar o index, e a quantidade de reads cai de 24000 para 16.

Nesse caso a diferença não é muito grande, mas quando trabalhamos com inner joins, e tudo mais, o número cresce exponencialmente. Tive um exemplo aqui que utilizando a consulta dinamica fez um pouco mais de cem mil registros, enquanto utilizando o ISNULL, ele passou de dois milhões.

Se alguem tiver mais alguma idéia, fico muito grato.