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

2021-12-20 00:00:00 sql parameters search sql-server stored-procedures

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

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.

这能做到吗?

推荐答案

虽然 COALESCE 技巧很巧妙，但我更喜欢的方法是:

While the COALESCE trick is neat, my preferred method is:

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 可以很容易地生成代码(并为表更改重新生成).

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

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

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

相关文章