Ignorar un parámetro NULO en T-SQL
Quiero ser capaz de pasar una lista de parámetros, e ignorar los que son NULL. De modo que la consulta está en efecto fingiendo que el filtro no está allí e ignorándolo.
Lo estaba haciendo así:
(@thing IS NULL or Thing=@thing)
¿Es esto correcto, y si es así, funcionaría mal? Parece ser mucho más lento que construir el SQL por separado.
¿Cuál es la forma óptima de hacer esto?
¡ARREGLADO! Mira la respuesta de Marc Gravell. En resumen, usar IS NULL muchas veces es un gran éxito de rendimiento.
9 answers
Una vez que obtienes más de un par de estos, entonces sí: comienza a ser bastante lento. En tales casos, tiendo a usar TSQL generado-es decir,
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
Tenga en cuenta que sp_ExecuteSQL almacena en caché los planes de consulta, por lo que cualquier consulta con los mismos args puede reutilizar potencialmente el plan.
La desventaja es que a menos que firme el SPROC, el llamante necesita permisos SELECT en la tabla (no solo permisos EXEC en el SPROC).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-10 15:29:28
Lo manejaría de esta manera.
WHERE Thing = ISNULL(@Thing, Thing)
Si solo está usando el parámetro como un filtro en la cláusula where, esto funcionará muy bien. Ignorará el parámetro si es null.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-10 14:12:23
Generalmente uso
WHERE (id = @id OR @id IS NULL)
AND (num = @num OR @num IS NULL)
Etc.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-10 14:57:27
No estoy seguro de si es la forma 'óptima', pero esto es exactamente lo que hago en mis procedimientos almacenados para los mismos propósitos. Mi intuición es que esto es más rápido que una consulta creada dinámicamente puramente desde el punto de vista del plan de ejecución. La otra opción es crear una consulta para cada combinación de estos" indicadores " que está pasando, pero que realmente no es tan escalable.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-10 14:11:45
Una técnica que he utilizado en el pasado para este escenario es utilizar la función COALESCE como parte de mi cláusula WHERE. Books Online proporcionará información más detallada sobre la función, pero aquí hay un fragmento de cómo puede usarla en el escenario que describió:
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)
La función COALESCE devolverá la primera expresión no nula de sus argumentos. En el ejemplo anterior, si alguno de los parámetros es null, la función COALESCE utilizará el valor en el campo subyacente.
Una advertencia importante para usar esta técnica es que los campos subyacentes en la tabla (que componen su cláusula where) no deben ser nullables.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-10 14:53:59
Mira el siguiente enlace en la sección titulada "El Caso de estudio: Búsqueda de órdenes". Esto explora todas las opciones en profundidad y debe darle una excelente visión general de los costos asociados con cada una de estas opciones. Advertencia, tenga mucho cuidado al usar COALESCE puede no devolver lo que cree que es.
Saludos,
Tim
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-11-30 14:56:01
Este es el método que normalmente uso. No veo ninguna razón para que sea ineficiente, ya que la instrucción debería cortocircuitar a true si @thing es null, y por lo tanto no requeriría un análisis de tabla. ¿Tiene alguna evidencia de que esta comparación está ralentizando su consulta? Si no, no me preocuparía por ello.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-10 14:13:05
Cuando declara los parámetros si establece un valor para ellos, como null en su caso, no necesita pasarles un valor a menos que, por supuesto, lo necesite. Uso esta habilidad para marcar si otra consulta necesita ser ejecutada en casos especiales cuando el parámetro no es null
Normalmente lo compruebo así
SI EL campo ES NULO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-02-10 14:13:27
Gracias, esto fue útil. He decidido utilizar el método sp_ExecuteSQL debido a las ventajas potenciales de rendimiento mencionadas. Tengo un punto de vista ligeramente diferente que usted puede encontrar ú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'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-10 21:19:48