我们认为什么是动态sql语句？
a) 我们认为什么是动态sql语句？
是否有任何 sql 语句可以动态地将子句甚至只是子句的一部分添加到 SQL 字符串？
b) 那么使用占位符动态提供值的参数化字符串是否也被视为动态 sql 语句？
谢谢
a) What do we consider as a dynamic sql statement?
Any sql statement that dynamically adds a clause(s) or even just a part of a clause to a SQL string?
b)Aren’t then parameterized strings that use placeholders for dynamically supplied values also considered dynamic sql statements?
thanx
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
动态 SQL 语句是在执行时构建的语句。重点在于陈述。因此，如果您只是在执行时提供值，那么它就不是动态 SQL。
A dynamic SQL statement is a statement that is built at execution time. The emphasis lies on statement. So, it isn't dynamic SQL if you just supply a value at execution time.
动态SQL语句通常是指使用字符串连接构造的语句。
参数化查询也是动态的，但不是在构造方面。您只能更改参数，但不能更改语句的结构，即添加
WHERE子句。参数化查询通常位于数据库级别，因此数据库可以缓存查询的执行计划并反复使用它。在第一种情况下不太可能，因为文本或 where 子句的顺序的简单更改可能会导致数据库无法识别先前缓存的执行计划并重新开始。
第一个构造也容易受到 SQL 注入的攻击，因为很难验证尝试注入恶意 SQL 的输入值。
Dynamic SQL statements generally refers those that are constructed using string concatenation.
Parameterized queries are also dynamic but not in terms of construct. You can only change parameters but you can't change the structure of the statement i.e add
WHEREclauses.Parameterized queries are often at the database level so the database can cache the execution plan of the query and use it over and over. Not quite possible in the first case since a simple change in the text or the order of the where clauses can cause the database to not recognize the previously cached execution plan and start over.
The first construct is also vulnerable to SQL injection since it is hard to validate input values for attempts to inject rogue SQL.
当然，任何涉及
EXEC (@sql)或EXEC sp_ExecuteSQL @sql, ...（即数据库本身动态）的内容都符合资格，但我想您可能会争辩说，任何在运行时生成的 SQL（而不是在构建/安装时固定）将符合条件。所以，是的，您可能会认为运行时生成但参数化正确的查询是“动态的”（例如，LINQ-to-SQL 生成的查询），但说实话，只要它不会让您遭受注入攻击，我就会这样做。不在乎名字;-p
Certainly anything involving
EXEC (@sql)orEXEC sp_ExecuteSQL @sql, ...(i.e. dynamic at the database itself) would qualify, but I guess you could argue that any SQL generated at runtime (rather than fixed at build / install) would qualify.So yes, you could argue that a runtime-generated, yet correctly parameterized query is "dynamic" (for example, LINQ-to-SQL generated queries), but to be honest as long as it doesn't expose you to injection attacks I don't care about the name ;-p
动态 sql 基本上就是任何直到运行时才完全构建的 sql。它是通过将运行时值连接到语句中即时生成的。可以是 sql 语句的任何部分
Dynamic sql is basically just any sql that is not fully constructed until runtime. Its generated on-the-fly by concatenating runtime values into a statement. Could be any part of an sql statement
A. 任何会导致数据库服务器将字符串计算为 SQL 的情况。
B. 不，因为它们仍然通过数据库驱动程序/提供商并得到清理。
A. Anything that will cause the DB server to evaluate strings as SQL.
B. No, as they still go through the DB driver/provider and get cleaned up.
对于b）点，您已经知道该语句并且传入了已知参数（希望类型安全而不是字符串文字）。
For point b) You already know the statement and you pass in known parameters (hopefully type safe and not string literals).
我认为动态 SQL 语句是在运行时接受新值以返回不同结果的语句。据我估计，“新值”可以是不同的 ORDER BY、新的 WHERE 标准、不同的字段选择等。
I consider a dynamic SQL statement to be one that accepts new values at runtime in order to return a different result. "New values", by my reckoning, can be a different ORDER BY, new WHERE criteria, different field selections, etc.
两者 - 在执行之前更改/定制的任何查询。
参数化查询（又名使用绑定变量）为查询提供不同的过滤条件。您可以使用
(my_variable IS NULL OR ...)，但OR性能在任何数据库和数据库上通常都很糟糕。该方法破坏了可控制性。动态 SQL 通常涉及定制查询以包含其他逻辑，例如仅在设置了特定参数时才需要包含的 JOIN。但是，存在一些限制，例如 IN 子句不支持转换逗号分隔字符串，因为它是选项列表 - 为此，您必须使用动态 SQL，或以其他方式处理逗号分隔列表（CLR，管道化到临时表中， ETC）。
Both - any query altered/tailored prior to execution.
Parameterized queries, AKA using bind variables, are supplying different filter criteria to the query. You can use
(my_variable IS NULL OR ...), but OR performance is generally terrible on any db & the approach destroys sargability.Dynamic SQL generally deals with tailoring the query to include other logic, like JOINs that only need to be included if a specific parameter is set. However, there are limitations like the IN clause not supporting converting a comma delimited string as it's list of options - for this you would have to use dynamic SQL, or handle the comma delimited list in another fashion (CLR, pipelined into a temp table, etc).
我明白你的意思，但是一个有点客观的标准定义了一种特定的情况，即动态 SQL 与准备好的语句是......
事实上，动态语句会导致 SQL 服务器完全评估查询，定义查询计划等。
使用准备好的语句，SQL 可以（并且除非明确要求）缓存查询计划（在某些情况下，甚至收集有关返回等的统计信息）。
[编辑：实际上，即使是动态 SQL 语句也会被缓存，但此类缓存的计划被重用的机会要小得多，因为需要重新接收完全相同的查询才能发生这种情况（与参数化的情况不同）即使具有不同的参数值，也会重用计划的查询，当然，除非“WITH RECOMPILE”）]
所以在问题的情况下，a）和b）都将被视为动态SQL，因为在b）的情况下，替换发生在 SQL 之外。这不是一份准备好的声明。 SQL 会将其视为一个全新的语句，因为它不知道您只是更改搜索值。
现在...除了动态 SQL 的以 SQL 为中心定义之外，区分各种形式的动态 SQL 可能很有用，例如您的 a) 和 b)动态 SQL 的名声不好已经有一段时间了，其中一些与 SQL 注入意识有关。我见过在存储过程中生成和执行动态 SQL 的应用程序，并且由于它在技术上是“SQL 端”，所以一些人倾向于接受它（尽管 SQL 注入尤其如此）可能尚未解决）...
我在这里想说的一点是，经常需要从各种上下文元素动态构建查询，并且最好建议在“应用程序”层中实现这一点（好吧，称之为应用程序端层，例如事实上，它可以而且应该与应用程序本身分开），其中编程语言和关联的数据结构通常比 T-SQL 等更容易、更具表现力。因此，在我看来，为了将其称为“数据端”而将其塞入 SQL 中并不是一件好事。
I see where you're going with this, but one somewhat objective criteria which defines a particular situation as Dynamic SQL vs. say a prepared statement is...
...the fact that dynamic statements cause the SQL server to fully evaluate the query, to define a query plan etc.
With prepared statements, SQL can (and does unless explicitly asked) cache the query plan (and in some cases, even gather statistics about the returns etc.).
[Edit: effectively, even dynamic SQL statements are cached, but such cached plans have a much smaller chance of being reused because the exact same query would need to be received anew for this to happen (unlike with parametrized queries where the plan is reused even with distinct parameter values and of course, unless "WITH RECOMPILE")]
So in the cases from the question, both a) and b) would be considered dynamic SQL, because, in the case of b), the substitution takes place outside of SQL. This is not a prepared statement. SQL will see it as a totally novel statement, as it doesn't know that you are merely changing the search values.
Now... beyond the SQL-centric definition of dynamic SQL, it may be useful to distinguish between various forms of dynamic SQL, such as say your a) and b) cases.
Dynamic SQL has had a bad rep for some time, some of it related to SQL injection awareness. I've seen applications where dynamic SQL is produced, and executed, within a Stored Procedure, and because it is technically "SQL-side" some people tended to accept it (even though SQL-injecting in particular may not have been addressed)...
The point I'm trying to make here is that building a query, dynamically, from various contextual elements is often needed, and one may be better advised to implement this in "application" layers (well, call it application-side layers, for indeed, it can and should be separate from application per-se), where the programming language and associated data structures are typically easier and more expressive than say T-SQL and such. So jamming this into SQL for the sake of calling it "Data-side" isn't a good thing, in my opinion.