Case When dentro do Where

SQL Server

17/06/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...

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.


Anilgome

Anilgome

Curtidas 0

Respostas

Emerson Nascimento

Emerson Nascimento

17/06/2008

me parece bem simples. basta tirar o case:
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 = ISNULL(@CD_CONSULTA, CIT.CD_CONSULTA)
    CIT.NM_FANTASIA_CLIENTE = ISNULL(@NM_FANTASIA_CLIENTE, CIT.NM_FANTASIA_CLIENTE)



GOSTEI 0
Emerson Nascimento

Emerson Nascimento

17/06/2008

me parece bem simples. basta tirar o case:
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 = ISNULL(@CD_CONSULTA, CIT.CD_CONSULTA) and
    CIT.NM_FANTASIA_CLIENTE = ISNULL(@NM_FANTASIA_CLIENTE, CIT.NM_FANTASIA_CLIENTE)


ou ainda:
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)



GOSTEI 0
Anilgome

Anilgome

17/06/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.

  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.


GOSTEI 0
POSTAR