具有可选"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屋！