Как мне создать хранимую процедуру, которая при необходимости будет искать столбцы?

Первой моей мыслью было написать такой запрос ...

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
       )

... что затем заставит вызывающего абонента предоставить битовый флаг, если они хотят выполнить поиск в определенном поле, а затем предоставить значение, если они должны искать его, но я не знаю, создает ли это неаккуратное предложение WHERE или если Я могу обойтись без оператора CASE в предложении WHERE.

Как видите, этот конкретный код находится на T-SQL, но я с удовольствием посмотрю и на некоторый код PL-SQL / MySQL и соответствующим образом адаптирован.

Самый эффективный способ реализовать этот тип поиска - использовать хранимую процедуру. Показанный здесь оператор создает процедуру, которая принимает необходимые параметры. Если значение параметра не указано, оно устанавливается в 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)

Взято с этой страницы: http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Я делал это раньше. Это работает хорошо.

Это можно сделать, но обычно эти процедуры для кухонной раковины приводят к плохим планам запросов.

Сказав все это, вот тактика, наиболее часто используемая для «необязательных» параметров. Обычный подход - рассматривать NULL как «пропущенный».

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)

Обновлено: Гораздо лучшим подходом были бы параметризованные запросы. Вот сообщение в блоге одного из ведущих мировых авторитетов в этой области, Франса Баумы из LLBLGen Pro:

Хранимые процедуры и динамические запросы

Статья Эрланда Соммарскога Условия динамического поиска в T-SQL - хороший справочник о том, как это сделать. Эрланд представляет ряд стратегий того, как сделать это без использования динамического SQL (просто блоки IF, OR, COALESCE и т. д.), И даже перечисляет характеристики производительности каждого метода.

В случае, если вам нужно укусить пулю и пройти путь динамического SQL, вы также должны прочитать Проклятие и благословение динамического SQL Эрланда, где он дает несколько советов о том, как правильно писать динамические SQL.

Уловка с COALESCE проста, но я предпочитаю:

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 + '%')

Такого рода SP могут быть легко сгенерированы в коде (и повторно сгенерированы для изменения таблиц).

У вас есть несколько вариантов обработки чисел - в зависимости от того, нужна ли вам точная семантика или семантика поиска.

Я бы предпочел метод NULL / COALESCE над AdHoc Queries, а затем протестировал бы, чтобы убедиться, что у вас нет проблем с производительностью.

Если окажется, что у вас медленно выполняются запросы, потому что при поиске по индексированным столбцам выполняется сканирование таблицы, вы всегда можете дополнить общую хранимую процедуру поиска дополнительными специальными процедурами, которые позволяют выполнять поиск по этим индексированным полям. Например, у вас может быть специальный SP, который выполняет поиск по идентификатору клиента или фамилии / имени.

Проблема использования метода COALESCE состоит в том, что если ваш столбец имеет значение NULL, передача условия поиска NULL (что означает игнорирование условия поиска) не вернет строку во многих базах данных.

Например, попробуйте следующий код на 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

Вы получите только две строки, потому что в строках, в которых столбец my_string имеет значение NULL, вы эффективно получаете:

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

Но, конечно, NULL не равно NULL.

Я стараюсь придерживаться:

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

Конечно, вы можете настроить это, чтобы использовать подстановочные знаки или что-то еще, что вы хотите сделать.

Копирую это из моего сообщения в блоге:

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

Напишите процедуру для вставки всех данных сотрудников, чьи имена начинаются с A в таблице ??

Мы можем использовать Generic @Search Parameter и передавать ему любое значение для поиска.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: --
-- Create date:
-- Description: --
-- =============================================
CREATE PROCEDURE [dbo].[usp_StudentList]
    @PageNumber INT    = 1, -- Paging parameter
    @PageSize   INT    = 10,-- Paging parameter
    @Search  VARCHAR(MAX) = NULL, --Generic Search Parameter
    @OrderBy VARCHAR(MAX) = 'FirstName', --Default Column Name 'FirstName' for records ordering
    @SortDir VARCHAR(MAX) = 'asc' --Default ordering 'asc' for records ordering
AS
BEGIN
    SET NOCOUNT ON;

    --Query required for paging, this query used to show total records
    SELECT COUNT(StudentId) AS RecordsTotal FROM Student

    SELECT Student.*, 
        --Query required for paging, this query used to show total records filtered
        COUNT(StudentId) OVER (PARTITION BY 1) AS RecordsFiltered 
    FROM Student
    WHERE 
    --Generic Search 
    -- Below is the column list to add in Generic Serach
    (@Search IS NULL OR Student.FirstName LIKE '%'+ @Search +'%')
    OR (@Search IS NULL OR Student.LastName LIKE '%'+ @Search +'%')
    --Order BY
    -- Below is the column list to allow sorting
    ORDER BY 
    CASE WHEN @SortDir = 'asc' AND @OrderBy = 'FirstName' THEN Student.FirstName END,
    CASE WHEN @SortDir = 'desc' AND @OrderBy = 'FirstName' THEN Student.FirstName  END DESC,
    CASE WHEN @SortDir = 'asc' AND @OrderBy = 'LastName' THEN Student.LastName END,
    CASE WHEN @SortDir = 'desc' AND @OrderBy = 'LastName' THEN Student.LastName  END DESC,
    OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;
END