SQL 如何创建可以选择搜索列的存储过程？

How do I create a stored procedure that will optionally search columns?

提问by Dillie-O

I'm working on an application for work that is going to query our employee database. The end users want the ability to search based on the standard name/department criteria, but they also want the flexibility to query for all people with the first name of "James" that works in the Health Department. The one thing I want to avoid is to simply have the stored procedure take a list of parameters and generate a SQL statement to execute, since that would open doors to SQL injection at an internal level.

我正在开发一个工作应用程序，它将查询我们的员工数据库。最终用户希望能够根据标准姓名/部门条件进行搜索，但他们还希望能够灵活地查询在卫生部门工作、名字为“James”的所有人员。我想要避免的一件事是简单地让存储过程获取参数列表并生成要执行的 SQL 语句，因为这会在内部级别打开 SQL 注入的大门。

Can this be done?

这能做到吗？

回答by Cade Roux

While the COALESCEtrick is neat, my preferred method is:

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

These kind of SPs can easily be code generated (and re-generated for table-changes).

这些类型的 SP 可以很容易地生成代码（并为表更改重新生成）。

You have a few options for handling numbers - depending if you want exact semantics or search semantics.

您有几个处理数字的选项 - 取决于您想要精确语义还是搜索语义。

回答by BoltBait

The most efficient way to implement this type of search is with a stored procedure. The statement shown here creates a procedure that accepts the required parameters. When a parameter value is not supplied it is set to NULL.

实现此类搜索的最有效方法是使用存储过程。此处显示的语句创建了一个接受所需参数的过程。如果未提供参数值，则将其设置为 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)

Taken from this page: http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

取自此页面：http: //www.sqlteam.com/article/implementing-a-dynamic-where-clause

I've done it before. It works well.

我以前做过。它运作良好。

回答by jop

Erland Sommarskog's article Dynamic Search Conditions in T-SQLis a good reference on how to do this. Erland presents a number of strategies on how to do this without using dynamic SQL (just plain IF blocks, OR, COALESCE, etc) and even lists out the performance characteristics of each technique.

Erland Sommarskog 的文章T-SQL中的动态搜索条件是有关如何执行此操作的很好参考。Erland 提出了许多关于如何在不使用动态 SQL（只是简单的 IF 块、OR、COALESCE 等）的情况下执行此操作的策略，甚至列出了每种技术的性能特征。

In case you have to bite the bullet and go through the Dynamic SQL path, you should also read Erland's Curse and Blessings of Dynamic SQLwhere he gives out some tips on how to properly write dynamic SQLs

如果您不得不咬紧牙关并通过动态 SQL 路径，您还应该阅读 Erland 的动态 SQL的诅咒和祝福，他给出了一些关于如何正确编写动态 SQL 的技巧

回答by Pittsburgh DBA

It can be done, but usually these kitchen-sink procedures result in some poor query plans.

可以做到，但通常这些厨房水槽程序会导致一些糟糕的查询计划。

Having said all that, here is the tactic most commonly used for "optional" parameters. The normal approach is to treat NULL as "ommitted".

说了这么多，这里是最常用于“可选”参数的策略。通常的方法是将 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)

EDIT: A far better approach would be parameterized queries. Here is a blog post from one of the world's foremost authorities in this domain, Frans Bouma from LLBLGen Pro fame:

编辑：更好的方法是参数化查询。这是该领域世界上最重要的权威之一，来自 LLBLGen Pro 的 Frans Bouma 的博客文章：

Stored Procedures vs. Dynamic Queries

存储过程与动态查询

回答by Tom H

Using the COALESCE method has a problem in that if your column has a NULL value, passing in a NULL search condition (meaning ignore the search condition) will not return the row in many databases.

使用 COALESCE 方法有一个问题，如果您的列具有 NULL 值，则传入 NULL 搜索条件（意味着忽略搜索条件）将不会在许多数据库中返回该行。

For example, try the following code on SQL Server 2000:

例如，在 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

You will only get back two rows because in the rows where the column my_string is NULL you are effective getting:

您只会返回两行，因为在 my_string 列为 NULL 的行中，您可以有效获取：

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

But of course, NULL does not equal NULL.

但当然，NULL 不等于 NULL。

I try to stick with:

我尝试坚持：

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

Of course, you can adjust that to use wild cards or whatever else you want to do.

当然，您可以调整它以使用通配符或其他任何您想做的事情。

回答by Manoj Pandey

Copying this from my blog post:

从我的博客文章中复制此内容：

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

回答by Ravindra Vairagi

We can use Generic @Search Parameter and pass any value to it for searching.

我们可以使用通用@Search 参数并将任何值传递给它进行搜索。

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

回答by Aheho

I would stick with the NULL/COALESCE method over AdHoc Queries, and then test to make sure you don't have performance problems.

我会坚持使用 NULL/COALESCE 方法而不是 AdHoc 查询，然后进行测试以确保您没有性能问题。

If it turns out that you have slow running queries because it's doing a table scan when you're searching on columns that are indexed, you could always supplement the generic search stored procedure with additional specific ones that allow searching on these indexed fields. For instance, you could have a special SP that does searches by CustomerID, or Last/First Name.

如果事实证明您的查询运行缓慢，因为当您搜索已索引的列时它正在执行表扫描，则您始终可以使用允许搜索这些索引字段的其他特定存储过程来补充通用搜索存储过程。例如，您可以有一个特殊的 SP，它按 CustomerID 或姓氏/名字进行搜索。

回答by Dillie-O

My first thought was to write a query something like this...

我的第一个想法是写一个这样的查询......

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
       )

...which would then have the caller provide a bit flag if they want to search a particular field and then supply the value if they are to search for it, but I don't know if this is creating a sloppy WHERE clause or if I can get away with a CASE statement in the WHERE clause.

...如果他们想搜索特定字段，然后调用者提供一个位标志，然后如果他们要搜索它，则提供该值，但我不知道这是否创建了一个草率的 WHERE 子句，或者如果我可以在 WHERE 子句中使用 CASE 语句。

As you can see this particular code is in T-SQL, but I'll gladly look at some PL-SQL / MySQL code as well and adapt accordingly.

正如您所看到的，这个特定的代码是在 T-SQL 中，但我也很乐意查看一些 PL-SQL/MySQL 代码并进行相应的调整。

回答by Raaj

Write a procedure to insert all employee data whose name start with A in table??

编写一个程序将所有名字以A开头的员工数据插入表中？？