tsql - tabla - retornar valor stored procedure sql server

¿Cómo puedo usar parámetros opcionales en un procedimiento almacenado de T-SQL?

Question

Estoy creando un procedimiento almacenado para hacer una búsqueda a través de una tabla. Tengo muchos campos de búsqueda diferentes, todos los cuales son opcionales. ¿Hay alguna manera de crear un procedimiento almacenado que maneje 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 trabajo. Sin embargo, ignora los registros donde FirstName, LastName o Title son NULL. Si no se especifica el título en los parámetros de búsqueda, quiero incluir registros donde Title sea NULL, lo mismo para FirstName y LastName. Sé que probablemente podría hacer esto con SQL dinámico, pero me gustaría evitar eso.

Answer 1

Cambiar dinámicamente las búsquedas basadas en los parámetros dados es un tema complicado y hacerlo de una forma u otra, incluso con una diferencia muy pequeña, puede tener implicaciones de rendimiento masivo. La clave es usar un índice, ignorar el código compacto, ignorar la preocupación por repetir el código, debe hacer un buen plan de ejecución de la consulta (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 por Erland Sommarskog

La maldición y las bendiciones de SQL dinámico por Erland Sommarskog

Si tiene la versión adecuada de SQL Server 2008 (SQL 2008 SP1 CU5 (10.0.2746) y posterior), puede usar este pequeño truco para usar realmente un índice:

Agregue OPTION (RECOMPILE) a su consulta, consulte el artículo de Erland y SQL Server resolverá el OR desde dentro (@LastName IS NULL OR LastName= @LastName) antes de (@LastName IS NULL OR LastName= @LastName) el plan de consulta en función de los valores de tiempo de ejecución de las variables locales, y un índice puede ser utilizado.

Esto funcionará para cualquier versión de SQL Server (devuelva los resultados correctos), pero solo incluya OPTION (RECOMPILE) si está en SQL 2008 SP1 CU5 (10.0.2746) y posterior. La OPCIÓN (RECOMPRA) recompilará su consulta, solo el verificador enumerado lo recompilará en función de los valores de tiempo de ejecución actuales de las variables locales, lo que le proporcionará el mejor rendimiento. Si no está en esa versión de SQL Server 2008, simplemente deje 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

Answer 2

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: construir dinámicamente la consulta en función de los parámetros proporcionados. P.ej:

Preparar

-- 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''

Pros:

• fácil de escribir y entender
• flexibilidad: genere fácilmente la consulta para filtraciones más complicadas (p. ej., TOP dinámica)

Contras:

• posibles problemas de rendimiento según los parámetros proporcionados, los índices y el volumen de datos

No es una respuesta directa, sino que está relacionada con el problema, también conocido como la gran visión

Por lo general, estos procedimientos almacenados de filtrado no flotan, pero se llaman desde alguna capa de servicio. Esto deja la opción de alejar la lógica comercial (filtrado) de SQL a la capa de servicio.

Un ejemplo es el uso de LINQ2SQL para generar la consulta en función de 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;
    }

Pros:

• consulta generada dinámicamente en base a los filtros proporcionados. No es necesario olfatear ni recompile parámetros
• algo más fácil de escribir para aquellos en el mundo OOP
• por lo general, es amigable con el rendimiento, ya que se emitirán consultas "simples" (aunque aún se necesitan índices apropiados)

Contras:

• Se pueden alcanzar las limitaciones de LINQ2QL y forzar una degradación a LINQ2Objects o volver a la solución de SQL puro según el caso
• la escritura descuidada de LINQ puede generar consultas terribles (o muchas consultas, si se cargan propiedades de navegación)

Answer 3

Esto también funciona:

    ...
    WHERE
        (FirstName IS NULL OR FirstName = ISNULL(@FirstName, FirstName)) AND
        (LastName IS NULL OR LastName = ISNULL(@LastName, LastName)) AND
        (Title IS NULL OR Title = ISNULL(@Title, Title))

Answer 4

Extienda su condición WHERE :

WHERE
    (FirstName = ISNULL(@FirstName, FirstName)
    OR COALESCE(@FirstName, FirstName, '''') = '''')
AND (LastName = ISNULL(@LastName, LastName)
    OR COALESCE(@LastName, LastName, '''') = '''')
AND (Title = ISNULL(@Title, Title)
    OR COALESCE(@Title, Title, '''') = '''')

es decir, combina diferentes casos con condiciones booleanas.

Answer 5

La respuesta de @KM es buena hasta donde llega, pero falla en el seguimiento completo de uno de sus primeros consejos;

... ignorar el código compacto, ignorar la preocupación por repetir el código, ...

Si busca obtener el mejor rendimiento, debe escribir una consulta personalizada para cada posible combinación de criterios opcionales. Esto puede sonar extremo, y si tiene muchos criterios opcionales, entonces podría ser, pero el rendimiento a menudo es una compensación entre el esfuerzo y los resultados. En la práctica, puede haber un conjunto común de combinaciones de parámetros que se pueden orientar con consultas a medida, 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 hechas a medida, la consulta es tan eficiente como puede ser: no hay impacto por los criterios de desabastecimiento. Además, los índices y otras mejoras de rendimiento se pueden orientar a consultas personalizadas específicas en lugar de intentar satisfacer todas las situaciones posibles.

Answer 6

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, dependa de los datos a veces mejor, cree una consulta dinámica y ejecútelos.