Case When dentro do Where

17/06/2008

0

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

Responder

Posts

17/06/2008

Emerson Nascimento

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)



Responder

17/06/2008

Emerson Nascimento

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)



Responder

17/06/2008

Anilgome

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.


Responder

Que tal ter acesso a um e-book gratuito que vai te ajudar muito nesse momento decisivo?

Ver ebook

Recomendado pra quem ainda não iniciou o estudos.

Eu quero
Ver ebook

Recomendado para quem está passando por dificuldades nessa etapa inicial

Eu quero

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar