带有可选“WHERE"的存储过程参数 [英] Stored Procedure with optional "WHERE" parameters

问题描述

我有一个表单，用户可以在其中指定各种参数来挖掘一些数据(状态、日期等).

I have a form where users can specify various parameters to dig through some data (status, date etc.).

我可以生成一个查询:

SELECT * FROM table WHERE:
status_id = 3
date = <some date>
other_parameter = <value>

等等.每个 WHERE 都是可选的(我可以选择 status = 3 的所有行，或 date = 10/10/1980 的所有行，或 status = 3 AND date = 10/10/1980 等的所有行).

etc. Each WHERE is optional (I can select all the rows with status = 3, or all the rows with date = 10/10/1980, or all the rows with status = 3 AND date = 10/10/1980 etc.).

给定大量参数，都是可选的，构成动态存储过程的最佳方法是什么?

Given a large number of parameters, all optional, what is the best way to make up a dynamic stored procedure?

我正在处理各种数据库，例如:MySQL、Oracle 和 SQLServer.

I'm working on various DB, such as: MySQL, Oracle and SQLServer.

推荐答案

最简单的方法之一:

SELECT * FROM table 
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))

等等.这完全消除了动态 sql 并允许您搜索一个或多个字段.通过消除动态 sql，您消除了关于 sql 注入的另一个安全问题.

etc. This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.

这篇关于带有可选“WHERE"的存储过程参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！