¿Cómo puedo utilizar parámetros opcionales en un procedimiento almacenado T-SQL?
Estoy creando un procedimiento almacenado para hacer una búsqueda a través de una tabla. Tengo muchos campos de búsqueda diferentes, todos ellos opcionales. ¿Hay alguna manera de crear un procedimiento almacenado que se encargue de esto? Digamos que tengo una tabla con cuatro campos: ID, Nombre, Apellido y Título. Podría hacer algo como esto:
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
FirstName = ISNULL(@FirstName, FirstName) AND
LastName = ISNULL(@LastName, LastName) AND
Title = ISNULL(@Title, Title)
END
Este tipo de obras. Sin embargo, ignora los registros donde Nombre, Apellido o Título son NULL. Si el Título no se especifica en los parámetros de búsqueda, quiero incluir registros donde el Título sea NULL; lo mismo para Nombre y Apellido. Sé que probablemente podría hacer esto con SQL dinámico, pero me gustaría evitarlo.
Cambiar dinámicamente las búsquedas en función de los parámetros dados es un tema complicado y hacerlo de una forma u otra, incluso con una diferencia muy pequeña, puede tener enormes implicaciones en el rendimiento. La clave es usar un índice, ignorar el código compacto, ignorar la preocupación por la repetición del código, debe hacer un buen plan de ejecución de consultas (usar un índice).
Lea esto y considere todos los métodos. Su mejor método dependerá de sus parámetros, sus datos, su esquema y su uso real:
Condiciones de búsqueda dinámica en T-SQL por Erland Sommarskog
La maldición y las bendiciones del SQL dinámico por Erland Sommarskog
Si tiene la versión adecuada de SQL Server 2008 (SQL 2008 SP1 CU5 (10.0.2746) y posteriores), puede utilizar este pequeño truco para utilizar un índice:
Agregue OPTION (RECOMPILE)a su consulta, consulte el artículo de Erland , y SQL Server resolverá el problema ORdesde dentro (@LastName IS NULL OR LastName= @LastName)antes de que se cree el plan de consulta en función de los valores de tiempo de ejecución de las variables locales y se pueda utilizar un índice.
Esto funcionará para cualquier versión de SQL Server (proporcionará resultados adecuados), pero solo incluirá la OPCIÓN (RECOMPILAR) si está en SQL 2008 SP1 CU5 (10.0.2746) y versiones posteriores. La OPCIÓN (RECOMPILE) recompilará su consulta, solo la versión enumerada la recompilará en función de los valores de tiempo de ejecución actuales de las variables locales, lo que le brindará el mejor rendimiento. Si no está en esa versión de SQL Server 2008, simplemente omita esa línea.
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
(@FirstName IS NULL OR (FirstName = @FirstName))
AND (@LastName IS NULL OR (LastName = @LastName ))
AND (@Title IS NULL OR (Title = @Title ))
OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
END
La respuesta de @KM es buena hasta donde llega, pero no sigue completamente uno de sus primeros consejos;
..., ignorar el código compacto, ignorar la preocupación por la repetición del código, ...
Si busca lograr el mejor rendimiento, debe escribir una consulta personalizada para cada combinación posible de criterios opcionales. Esto puede parecer extremo, y si tiene muchos criterios opcionales, puede que lo sea, pero el rendimiento suele ser una compensación entre esfuerzo y resultados. En la práctica, puede haber un conjunto común de combinaciones de parámetros al que se pueden dirigir consultas personalizadas y luego una consulta genérica (según las otras respuestas) para todas las demás combinaciones.
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
IF (@FirstName IS NOT NULL AND @LastName IS NULL AND @Title IS NULL)
-- Search by first name only
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
FirstName = @FirstName
ELSE IF (@FirstName IS NULL AND @LastName IS NOT NULL AND @Title IS NULL)
-- Search by last name only
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
LastName = @LastName
ELSE IF (@FirstName IS NULL AND @LastName IS NULL AND @Title IS NOT NULL)
-- Search by title only
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
Title = @Title
ELSE IF (@FirstName IS NOT NULL AND @LastName IS NOT NULL AND @Title IS NULL)
-- Search by first and last name
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
FirstName = @FirstName
AND LastName = @LastName
ELSE
-- Search by any other combination
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
(@FirstName IS NULL OR (FirstName = @FirstName))
AND (@LastName IS NULL OR (LastName = @LastName ))
AND (@Title IS NULL OR (Title = @Title ))
END
La ventaja de este enfoque es que en los casos comunes manejados por consultas personalizadas, la consulta es lo más eficiente posible: los criterios no proporcionados no afectan. Además, los índices y otras mejoras de rendimiento pueden orientarse a consultas específicas y personalizadas en lugar de intentar satisfacer todas las situaciones posibles.
Puedes hacerlo en el siguiente caso,
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
(@FirstName IS NULL OR FirstName = @FirstName) AND
(@LastNameName IS NULL OR LastName = @LastName) AND
(@Title IS NULL OR Title = @Title)
END
sin embargo, depender de los datos a veces es mejor crear consultas dinámicas y ejecutarlas.
Cinco años tarde a la fiesta.
Se menciona en los enlaces proporcionados de la respuesta aceptada, pero creo que merece una respuesta explícita sobre SO: crear dinámicamente la consulta en función de los parámetros proporcionados. P.ej:
Configuración
-- drop table Person
create table Person
(
PersonId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Person PRIMARY KEY,
FirstName NVARCHAR(64) NOT NULL,
LastName NVARCHAR(64) NOT NULL,
Title NVARCHAR(64) NULL
)
GO
INSERT INTO Person (FirstName, LastName, Title)
VALUES ('Dick', 'Ormsby', 'Mr'), ('Serena', 'Kroeger', 'Ms'),
('Marina', 'Losoya', 'Mrs'), ('Shakita', 'Grate', 'Ms'),
('Bethann', 'Zellner', 'Ms'), ('Dexter', 'Shaw', 'Mr'),
('Zona', 'Halligan', 'Ms'), ('Fiona', 'Cassity', 'Ms'),
('Sherron', 'Janowski', 'Ms'), ('Melinda', 'Cormier', 'Ms')
GO
Procedimiento
ALTER PROCEDURE spDoSearch
@FirstName varchar(64) = null,
@LastName varchar(64) = null,
@Title varchar(64) = null,
@TopCount INT = 100
AS
BEGIN
DECLARE @SQL NVARCHAR(4000) = '
SELECT TOP ' + CAST(@TopCount AS VARCHAR) + ' *
FROM Person
WHERE 1 = 1'
PRINT @SQL
IF (@FirstName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @FirstName'
IF (@LastName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @LastName'
IF (@Title IS NOT NULL) SET @SQL = @SQL + ' AND Title = @Title'
EXEC sp_executesql @SQL, N'@TopCount INT, @FirstName varchar(25), @LastName varchar(25), @Title varchar(64)',
@TopCount, @FirstName, @LastName, @Title
END
GO
Uso
exec spDoSearch @TopCount = 3
exec spDoSearch @FirstName = 'Dick'
Ventajas:
- fácil de escribir y entender
- flexibilidad: genere fácilmente la consulta para filtrados más complicados (por ejemplo, TOP dinámico)
Contras:
- Posibles problemas de rendimiento dependiendo de los parámetros, índices y volumen de datos proporcionados.
No es una respuesta directa, pero está relacionada con el problema, también conocido como el panorama general.
Por lo general, estos procedimientos almacenados de filtrado no flotan, sino que se llaman desde alguna capa de servicio. Esto deja la opción de trasladar la lógica empresarial (filtrado) de SQL a la capa de servicio.
Un ejemplo es usar LINQ2SQL para generar la consulta según los filtros proporcionados:
public IList<SomeServiceModel> GetServiceModels(CustomFilter filters)
{
var query = DataAccess.SomeRepository.AllNoTracking;
// partial and insensitive search
if (!string.IsNullOrWhiteSpace(filters.SomeName))
query = query.Where(item => item.SomeName.IndexOf(filters.SomeName, StringComparison.OrdinalIgnoreCase) != -1);
// filter by multiple selection
if ((filters.CreatedByList?.Count ?? 0) > 0)
query = query.Where(item => filters.CreatedByList.Contains(item.CreatedById));
if (filters.EnabledOnly)
query = query.Where(item => item.IsEnabled);
var modelList = query.ToList();
var serviceModelList = MappingService.MapEx<SomeDataModel, SomeServiceModel>(modelList);
return serviceModelList;
}
Ventajas:
- Consulta generada dinámicamente basada en los filtros proporcionados. No se necesitan rastreos de parámetros ni sugerencias de recompilación
- algo más fácil de escribir para aquellos en el mundo de la programación orientada a objetos
- normalmente es amigable con el rendimiento, ya que se emitirán consultas "simples" (aunque aún se necesitan índices apropiados)
Contras:
- Es posible que se alcancen las limitaciones de LINQ2QL y se obligue a bajar a LINQ2Objects o volver a una solución SQL pura, según el caso.
- La escritura descuidada de LINQ puede generar consultas horribles (o muchas consultas, si se cargan las propiedades de navegación)
