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...
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.
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
Curtir tópico
+ 0
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:
ou ainda:
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.
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.
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
Clique aqui para fazer login e interagir na Comunidade :)
