WHERE 子句中的动态条件
我有一个存储过程，想知道是否可以基于参数构建动态 where 条件。
假设我有这个查询：
SELECT *
FROM tbl_Users
现在，我有一个名为 @username 的参数，我想用它来构建动态 where 条件（通过我的程序可能会是 1 个或多个条件）。为了实现类似的目标，我使用以下语句：
SELECT *
FROM tbl_Users
@username -- where this parameter might hold a condition string such as "Where usr_Username = 5 and usr_first_name like '%Frank%' etc
是否可以做这样的事情？
I have a stored procedure and would like to know if its possible to build up a dynamic where condition based on a parameter.
Lets say I have this query:
SELECT *
FROM tbl_Users
Now, I have a parameter called @username, which I would like to use to build up a dynamic where condition (which through my program might be 1 or more conditions). To achieve something like that I use the following statement:
SELECT *
FROM tbl_Users
@username -- where this parameter might hold a condition string such as "Where usr_Username = 5 and usr_first_name like '%Frank%' etc
Is it possible to do something like this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
为此，您必须使用动态 sql。
它会运行如下：
You're going to have to break into dynamic sql for this.
it would run something like this:
我不确定我理解你，但如果我的理解是正确的，你可以执行以下操作（注意：注入漏洞）
I'm not certain I understand you, but if my understanding is correct, you can do the following (NOTICE: injection vulnerable)
据我所知，这是行不通的。您将需要生成要执行的脚本并使用 exec 命令。
From what I know, this is not going to work. You're going to need to generate the script you want to execute and use the exec command.
您确实不应该将 SQL 关键字和参数连接成一个字符串，如上面的一些响应所示，因为这为 SQL 注入打开了大门（其中之一）贡献者实际上已经指出了这一点，这是一个明智的警告！）。
相反，您应该参数化您的 SQL 并执行系统 SP sp_executesql。
StackOverflow 发布中展示了一个非常好的代码示例。
You are really not supposed to be concatenating SQL keywords and parameters into one single string as shown in some of the responses above for reasons of opening the doors to SQL injection (One of the contributors actually called it out. That's a wise Warning!).
Instead, you are supposed to parameterize your SQL and execute the system SP sp_executesql.
A very good code example is shown in this StackOverflow posting.