Ignorando um parâmetro NULL no T-SQL

Eu quero ser capaz de passar em uma lista de parâmetros e ignorar os que são NULL. Assim, a consulta está, na verdade, fingindo que o filtro não está lá e ignorando-o.

Eu estava fazendo assim:

(@thing IS NULL or Thing=@thing) 

Isso está certo e, em caso afirmativo, seria ruim? Parece ser muito mais lento do que construir o SQL separadamente.

Qual é a melhor maneira de fazer isso?

FIXO! Veja a resposta de Marc Gravell. Em resumo, usar IS NULL muitas vezes é um grande sucesso no desempenho.

Uma vez que você tenha mais do que alguns deles, então sim: ele começa a ficar bem lento. Nesses casos, eu uso o TSQL gerado – ie

 DECLARE @sql nvarchar(4000) SET @sql = /* core query */ IF @name IS NOT NULL SET @sql = @sql + ' AND foo.Name = @name' IF @dob IS NOT NULL SET @sql = @sql + ' AND foo.DOB = @dob' // etc EXEC sp_ExecuteSQL @sql, N'@name varchar(100), @dob datetime', @name, @dob 

etc

Observe que o sp_ExecuteSQL armazena em cache os planos de consulta, portanto, qualquer consulta com os mesmos argumentos pode potencialmente reutilizar o plano.

A desvantagem é que, a menos que você assine o SPROC, o chamador precisa de permissions SELECT na tabela (não apenas permissions EXEC no SPROC).

Eu lidaria com isso dessa maneira.

 WHERE Thing = ISNULL(@Thing, Thing) 

Se você está apenas usando o parâmetro como um filtro na cláusula where, isso funcionará muito bem. Ele irá ignorar o parâmetro se for nulo.

Eu geralmente uso

 WHERE (id = @id OR @id IS NULL) AND (num = @num OR @num IS NULL) 

etc.

Eu não tenho certeza se é a maneira ‘ideal’, mas isso é exatamente o que eu faço em meus stored procedures para os mesmos fins. Minha intuição é que isso é mais rápido do que uma consulta criada dinamicamente, puramente do ponto de vista do plano de execução. A outra opção é criar uma consulta para cada combinação dessas “flags” que você está passando, mas isso não é escalável.

Uma técnica que usei no passado para esse cenário é utilizar a function COALESCE como parte da cláusula WHERE. Books Online fornecerá informações mais detalhadas sobre a function, mas aqui está um trecho de como você pode usá-la no cenário descrito:

 create procedure usp_TEST_COALESCE ( @parm1 varchar(32) = null, @parm2 varchar(32) = null, @parm3 int = null ) AS SELECT * FROM [TableName] WHERE Field1 = COALESCE(@parm1, Field1) AND Field2 = COALESCE(@parm2, Field2) AND Field3 = COALESCE(@parm3, Field3) 

A function COALESCE retornará a primeira expressão não nula de seus argumentos. No exemplo acima, se algum dos parâmetros for nulo, a function COALESCE usará o valor no campo subjacente.

Uma ressalva importante para o uso dessa técnica é que os campos subjacentes na tabela (que compõem sua cláusula where) precisam ser não anuláveis.

Veja o seguinte link na seção intitulada “O Estudo de Caso: Pesquisando Pedidos”. Isso explora todas as opções em profundidade e deve fornecer uma excelente visão geral dos custos associados a cada uma dessas opções. Atenção, tenha muito cuidado ao usar o COALESCE, ele pode não retornar o que você acha que é.

Saudações,

Tim

Esse é o método que eu geralmente uso. Não vejo razão para que seja ineficiente, pois a instrução deve causar um curto-circuito para true se @thing for null e, portanto, não exigiria uma varredura de tabela. Você tem alguma evidência de que essa comparação está retardando sua consulta? Se não, eu não me preocuparia com isso.

Quando você declara os parâmetros se você definir um valor para eles, como null no seu caso, você não precisa passar um valor para eles, a menos que você precise. Eu uso essa habilidade para sinalizar se outra consulta precisa ser executada em casos especiais quando o parâmetro não é nulo

Eu costumo apenas verificar isso assim

IF campo é NULL

Obrigado, isso foi útil. Eu decidi usar o método sp_ExecuteSQL devido às vantagens potenciais de desempenho mencionadas. Eu tenho um pouco diferente sobre isso que você pode achar útil.

 DECLARE @sql nvarchar(4000) DECLARE @where nvarchar(1000) ='' SET @sql = 'SELECT * FROM MyTable' IF @Param1 IS NOT NULL SET @where = @where + ' AND Field1 = @Param1' IF @Param2 IS NOT NULL SET @where = @where + ' AND Field2 = @Param2' IF @Param3 IS NOT NULL SET @where = @where + ' AND Field3 = @Param3' -- Add WHERE if where clause exists, 1=1 is included because @where begins with AND IF @where <> '' SET @sql = @sql + ' WHERE 1=1' + @where --Note that we could also create order parameters and append here SET @sql = @sql + ' ORDER BY Field1'