tabla - ver codigo procedimiento almacenado sql server

¿Cómo creo un procedimiento almacenado que opcionalmente buscará columnas?

Question

Estoy trabajando en una aplicación para el trabajo que va a consultar nuestra base de datos de empleados. Los usuarios finales desean tener la capacidad de buscar según los criterios estándar de nombre / departamento, pero también quieren la flexibilidad para consultar a todas las personas con el nombre de "James" que trabaja en el Departamento de Salud. Lo único que quiero evitar es simplemente hacer que el procedimiento almacenado tome una lista de parámetros y generar una instrucción SQL para ejecutar, ya que eso abriría las puertas a la inyección SQL a nivel interno.

Se puede hacer esto?

Answer 1

Mi primer pensamiento fue escribir una consulta como esta ...

SELECT EmpId, NameLast, NameMiddle, NameFirst, DepartmentName
  FROM dbo.Employee
       INNER JOIN dbo.Department ON dbo.Employee.DeptId = dbo.Department.Id
 WHERE IdCrq IS NOT NULL
       AND
       (
          @bitSearchFirstName = 0
          OR
          Employee.NameFirst = @vchFirstName
       )
       AND
       (
          @bitSearchMiddleName = 0
          OR
          Employee.NameMiddle = @vchMiddleName
       )
       AND
       (
          @bitSearchFirstName = 0
          OR
          Employee.NameLast = @vchLastName
       )
       AND
       (
          @bitSearchDepartment = 0
          OR
          Department.Id = @intDeptID
       )

... lo que haría que la persona que llama proporcione una bandera de bit si quieren buscar un campo en particular y luego proporcionar el valor si desean buscarlo, pero no sé si esto está creando una cláusula WHERE descuidada o si Puedo salirme con una declaración CASE en la cláusula WHERE.

Como puede ver, este código en particular está en T-SQL, pero con mucho gusto también veré algún código PL-SQL / MySQL y lo adaptaré en consecuencia.

Answer 2

El artículo de Erland Sommarskog Dynamic Search Conditions in T-SQL es una buena referencia sobre cómo hacer esto. Erland presenta una serie de estrategias sobre cómo hacer esto sin usar SQL dinámico (simplemente bloques IF, O, COALESCE, etc.) e incluso enumera las características de rendimiento de cada técnica.

En caso de que tenga que morder la bala y seguir la ruta de Dynamic SQL, también debe leer Erland''s Curse y Blessings of Dynamic SQL, donde da algunos consejos sobre cómo escribir SQLs dinámicamente

Answer 3

El uso del método COALESCE tiene un problema porque si su columna tiene un valor NULL, pasar una condición de búsqueda NULL (es decir, ignorar la condición de búsqueda) no devolverá la fila en muchas bases de datos.

Por ejemplo, intente con el siguiente código en SQL Server 2000:

CREATE TABLE dbo.Test_Coalesce (
    my_id   INT NOT NULL IDENTITY,
    my_string   VARCHAR(20) NULL )
GO
INSERT INTO dbo.Test_Coalesce (my_string) VALUES (NULL)
INSERT INTO dbo.Test_Coalesce (my_string) VALUES (''t'')
INSERT INTO dbo.Test_Coalesce (my_string) VALUES (''x'')
INSERT INTO dbo.Test_Coalesce (my_string) VALUES (NULL)
GO
DECLARE @my_string  VARCHAR(20)
SET @my_string = NULL
SELECT * FROM dbo.Test_Coalesce WHERE my_string = COALESCE(@my_string, my_string)
GO

Solo obtendrá dos filas atrás porque en las filas donde la columna my_string es NULL, obtiene los siguientes resultados:

my_string = COALESCE(@my_string, my_string) =>
my_string = COALESCE(NULL, my_string) =>
my_string = my_string =>
NULL = NULL

Pero, por supuesto, NULL no es igual a NULL.

Intento seguir con:

SELECT
     my_id,
     my_string
FROM
     dbo.Test_Coalesce
WHERE
     (@my_string IS NULL OR my_string = @my_string)

Por supuesto, puede ajustar eso para usar comodines o cualquier otra cosa que quiera hacer.

Answer 4

La forma más eficiente de implementar este tipo de búsqueda es con un procedimiento almacenado. La declaración que se muestra aquí crea un procedimiento que acepta los parámetros requeridos. Cuando no se proporciona un valor de parámetro, se establece en NULL.

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
@Cus_Name varchar(30) = NULL,
@Cus_City varchar(30) = NULL,
@Cus_Country varchar(30) =NULL
AS
SELECT Cus_Name,
       Cus_City,
       Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

Tomado de esta página: http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Lo he hecho antes. Funciona bien.

Answer 5

Me quedaría con el método NULL / COALESCE sobre AdHoc Queries, y luego probaré para asegurarme de que no tengas problemas de rendimiento.

Si resulta que tiene consultas de ejecución lenta porque está realizando un análisis de tabla cuando está buscando en columnas que están indexadas, siempre puede complementar el procedimiento almacenado de búsqueda genérica con los específicos adicionales que permiten buscar en estos campos indexados. Por ejemplo, podría tener un SP especial que realice búsquedas por ID de cliente o Apellido / Nombre.

Answer 6

Se puede hacer, pero generalmente estos procedimientos de sumidero de cocina resultan en algunos planes de consulta pobres.

Habiendo dicho todo eso, esta es la táctica más comúnmente utilizada para los parámetros "opcionales". El enfoque normal es tratar a NULL como "omitido".

SELECT
  E.EmployeeID,
  E.LastName,
  E.FirstName
WHERE
  E.FirstName = COALESCE(@FirstName, E.FirstName) AND
  E.LastName = COALESCE(@LastName, E.LastName) AND
  E.DepartmentID = COALESCE(@DepartmentID, E.DepartmentID)

EDITAR: Un enfoque mucho mejor sería consultas parametrizadas. Aquí hay una publicación de blog de una de las principales autoridades del mundo en este ámbito, Frans Bouma, de la fama de LLBLGen Pro:

Procedimientos almacenados frente a consultas dinámicas

Answer 7

Mientras que el truco COALESCE es limpio, mi método preferido es:

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
    @Cus_Name varchar(30) = NULL
    ,@Cus_City varchar(30) = NULL
    ,@Cus_Country varchar(30) = NULL
    ,@Dept_ID int = NULL
    ,@Dept_ID_partial varchar(10) = NULL
AS
SELECT Cus_Name
       ,Cus_City
       ,Cus_Country
       ,Dept_ID
FROM Customers
WHERE (@Cus_Name IS NULL OR Cus_Name LIKE ''%'' + @Cus_Name + ''%'')
      AND (@Cus_City IS NULL OR Cus_City LIKE ''%'' + @Cus_City + ''%'')
      AND (@Cus_Country IS NULL OR Cus_Country LIKE ''%'' + @Cus_Country + ''%'')
      AND (@Dept_ID IS NULL OR Dept_ID = @DeptID)
      AND (@Dept_ID_partial IS NULL OR CONVERT(varchar, Dept_ID) LIKE ''%'' + @Dept_ID_partial + ''%'')

Este tipo de SP se puede generar fácilmente con código (y volver a generarse para cambios de tabla).

Tiene algunas opciones para manejar números, dependiendo de si quiere semántica exacta o semántica de búsqueda.

Answer 8

Copiando esto desde mi blog:

USE [AdventureWorks]
GO

CREATE PROCEDURE USP_GET_Contacts_DynSearch
(
    -- Optional Filters for Dynamic Search
    @ContactID          INT = NULL, 
    @FirstName          NVARCHAR(50) = NULL, 
    @LastName           NVARCHAR(50) = NULL, 
    @EmailAddress       NVARCHAR(50) = NULL, 
    @EmailPromotion     INT = NULL, 
    @Phone              NVARCHAR(25) = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE
        @lContactID         INT, 
        @lFirstName         NVARCHAR(50), 
        @lLastName          NVARCHAR(50), 
        @lEmailAddress      NVARCHAR(50), 
        @lEmailPromotion    INT, 
        @lPhone             NVARCHAR(25)

    SET @lContactID         = @ContactID
    SET @lFirstName         = LTRIM(RTRIM(@FirstName))
    SET @lLastName          = LTRIM(RTRIM(@LastName))
    SET @lEmailAddress      = LTRIM(RTRIM(@EmailAddress))
    SET @lEmailPromotion    = @EmailPromotion
    SET @lPhone             = LTRIM(RTRIM(@Phone))

    SELECT
        ContactID, 
        Title, 
        FirstName, 
        MiddleName, 
        LastName, 
        Suffix, 
        EmailAddress, 
        EmailPromotion, 
        Phone
    FROM [Person].[Contact]
    WHERE
        (@lContactID IS NULL OR ContactID = @lContactID)
    AND (@lFirstName IS NULL OR FirstName LIKE ''%'' + @lFirstName + ''%'')
    AND (@lLastName IS NULL OR LastName LIKE ''%'' + @lLastName + ''%'')
    AND (@lEmailAddress IS NULL OR EmailAddress LIKE ''%'' + @lEmailAddress + ''%'')
    AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
    AND (@lPhone IS NULL OR Phone = @lPhone)
    ORDER BY ContactID

END
GO

Answer 9

¿Escribir un procedimiento para insertar todos los datos de los empleados cuyo nombre comience con A en la tabla?