Как создать хранимую процедуру, которая будет необязательно искать столбцы?
Я работаю над приложением для работы, которое собирается запросить базу данных наших сотрудников. Конечные пользователи хотят иметь возможность поиска на основе стандартных критериев имени / отдела, но они также хотят гибкости для запроса всех людей с именем "Джеймс", которое работает в Департаменте здравоохранения. Единственное, чего я хочу избежать, - это просто заставить хранимую процедуру взять список параметров и сгенерировать оператор SQL для выполнения, поскольку это откроет двери для внедрения SQL на внутреннем уровне.
Можно ли это сделать?
10 ответов
В то время как 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.
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
Я делал это раньше. Это работает хорошо.
Статья Эрланда Соммарскога " Условия динамического поиска в T-SQL" - хороший справочник о том, как это сделать. Эрланд представляет ряд стратегий о том, как сделать это без использования динамического SQL (просто простые блоки IF, OR, COALESCE и т. Д.), И даже перечисляет характеристики производительности каждого метода.
В случае, если вам нужно укусить пулю и пройти путь динамического SQL, вам также следует прочитать " Проклятие Эрланда и благословения динамического SQL", где он дает несколько советов о том, как правильно писать динамические SQL.
Это может быть сделано, но обычно эти кухонные процедуры приводят к плохим планам запросов.
Сказав все это, вот тактика, наиболее часто используемая для "необязательных" параметров. Обычный подход состоит в том, чтобы рассматривать 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:
Использование метода 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
Мы можем использовать 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
Я бы придерживался метода NULL/COALESCE над AdHoc Queries, а затем протестировал, чтобы убедиться, что у вас нет проблем с производительностью.
Если выясняется, что у вас медленные запросы, потому что при поиске по индексированным столбцам выполняется сканирование таблицы, вы всегда можете дополнить общую хранимую процедуру поиска дополнительными конкретными, которые позволяют выполнять поиск по этим индексированным полям. Например, у вас может быть специальный SP, который выполняет поиск по CustomerID или по фамилии / имени.
Моей первой мыслью было написать запрос примерно так...
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 и соответствующим образом адаптирую его.
Напишите процедуру для вставки всех данных о сотрудниках, чье имя начинается с A в таблице??