blocks|key|670217|text|动态SQL语句通常是指使用字符串连接构造的语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|670218|"SELECT+name+FROM+names+WHERE+id="+%2B+this.id;
"SELECT+name+FROM+names+WHERE+id="+%2B+this.id+%2B+"+AND+age="+this.age;|code-block|syntax|javascript|670219|参数化查询也是动态的，但不是在构造方面。您只能更改参数，但不能更改语句的结构，即添加WHERE子句。|offset|length|style|CODE|670220|参数化查询通常位于数据库级别，因此数据库可以缓存查询的执行计划并反复使用它。在第一种情况下不太可能，因为where子句的文本或顺序的简单更改可能会导致数据库无法识别以前缓存的执行计划并重新开始。|670221|第一个构造也容易受到SQL注入的攻击，因为很难验证尝试注入恶意SQL的输入值。|670222|entityMap^0|0|0|16|5|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Dynamic SQL statements generally refers those that are constructed using string concatenation. 

<pre><code>"SELECT name FROM names WHERE id=" + this.id;
"SELECT name FROM names WHERE id=" + this.id + " AND age=" this.age;
</code></pre>

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 <code>WHERE</code> clauses.

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.

blocks|key|670106|text|动态SQL语句是在执行时生成的语句。重点在于语句。因此，如果您只是在执行时提供一个值，那么它就不是动态SQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|670107|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1903992|text|当然，任何涉及EXEC+(@sql)或EXEC+sp_ExecuteSQL+@sql,+...+(即数据库本身的动态)都是合格的，但我猜您可能会认为，在运行时生成的任何SQL+(而不是在构建/安装时修复的)都是合格的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1903993|因此，是的，您可能会认为运行时生成的、但正确参数化的查询是“动态的”(例如，LINQ-+to+-SQL生成的查询)，但老实说，只要它不会使您受到注入攻击，我并不关心名称;-p|1903994|entityMap^0|7|B|J|S|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

Certainly anything involving <code>EXEC (@sql)</code> or <code>EXEC sp_ExecuteSQL @sql, ...</code> (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

blocks|key|601080|text|动态sql基本上就是直到运行时才完全构造的任何sql。它是通过将运行时值连接到语句中而动态生成的。可以是sql语句的任何部分|type|unstyled|depth|inlineStyleRanges|entityRanges|data|601081|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1904003|text|答:任何会导致DB服务器将字符串作为SQL进行计算的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1904004|B.不会，因为它们仍会通过DB驱动程序/提供程序进行清理。|1904005|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1904030|text|对于b点)，您已经知道了语句，并且传入了已知的参数(希望类型安全，而不是字符串文字)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1904031|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For point b) You already know the statement and you pass in known parameters (hopefully type safe and not string literals).

blocks|key|1904054|text|我明白您的意图，但有一个比较客观的标准，它将特定情况定义为动态SQL，而不是预准备语句……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1904055|动态语句使SQL+server充分地计算查询、定义查询计划等这一事实..|1904056|使用预准备语句，SQL可以(除非明确要求)缓存查询计划(在某些情况下，甚至可以收集有关返回的统计信息等)。|1904057|编辑：实际上，即使是动态SQL语句也会被缓存，但这样的缓存计划被重用的机会要小得多，因为需要重新接收完全相同的查询才能实现这一点(与参数化查询不同，在参数化查询中，即使使用不同的参数值，计划也会被重用，当然，除非"WITH+RECOMPILE")|offset|length|style|BOLD|1904058|因此，在问题中的情况下，a)和b)都将被视为动态SQL，因为在b)的情况下，替换发生在SQL之外。这不是准备好的语句。SQL会将其视为一个全新的语句，因为它不知道您只是在更改搜索值。|1904059|现在..。除了以SQL为中心的动态定义之外，区分各种形式的动态可能很有用，比如a)和b)情况。|1904060|动态SQL的名声不好已经有一段时间了，其中一些与SQL注入意识有关。我见过在存储过程中生成和执行动态SQL的应用程序，因为它在技术上是“SQL端”的，所以有些人倾向于接受它(尽管SQL注入可能还没有得到解决)……|1904061|我在这里试图说明的一点是，经常需要从各种上下文元素动态地构建查询，建议在“应用程序”层中实现这一点(好吧，称之为应用程序端层，因为实际上，它可以而且应该与应用程序本身分开)，在这种情况下，编程语言和相关的数据结构通常比T-SQL等更容易、更有表现力。因此，在我看来，为了将其称为“数据端”而将其塞进SQL中并不是一件好事。|1904062|entityMap^0|0|0|0|0|2|0|0|H|E|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Y|8|@$H|Z|I|10|J|K]]|9|@]|A|$]]|$1|L|3|M|5|6|7|11|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|12|8|@$H|13|I|14|J|K]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|15|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|16|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|U|$]]

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.

blocks|key|2078259|text|我认为动态SQL语句是在运行时接受新值以返回不同结果的语句。根据我的估计，“新值”可以是不同的ORDER+BY、新的WHERE标准、不同的字段选择等。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2078260|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|601350|text|sql+)我们认为什么是动态|type|unstyled|depth|inlineStyleRanges|entityRanges|data|601351|语句？是否有任何sql语句可以动态地向SQL字符串中添加一个子句，甚至只添加一个子句的一部分？|blockquote|601352|601353|601354|Both+-在执行之前更改/定制的任何查询。|601355|sql+)那么使用占位符表示动态提供的值的参数化字符串不也被认为是动态|601356|语句吗？|601357|601358|601359|参数化查询，即使用绑定变量的查询，为查询提供不同的过滤条件。您可以使用(my_variable+IS+NULL+OR+...)，但是OR在任何数据库上的性能通常都很糟糕&这种方法会破坏sargability。|offset|length|style|CODE|601360|动态SQL通常处理定制查询以包括其他逻辑，如仅在设置了特定参数时才需要包括的联接。然而，有一些限制，比如IN子句不支持将逗号分隔的字符串转换为它的选项列表-为此，您必须使用动态SQL，或者以另一种方式处理逗号分隔的列表(CLR，管道到临时表等)。|601361|entityMap^0|0|0|0|0|0|0|0|0|0|Z|S|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$]]|$1|E|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|14|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|15|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|17|8|@$Q|18|R|19|S|T]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1A|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|X|$]]

<blockquote>
 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?
</blockquote>

Both - any query altered/tailored prior to execution.

<blockquote>
 b)Aren’t then parameterized strings that use placeholders for dynamically supplied values also considered dynamic sql statements? 
</blockquote>

Parameterized queries, AKA using bind variables, are supplying different filter criteria to the query. You can use <code>(my_variable IS NULL OR ...)</code>, but OR performance is generally terrible on any db &amp; 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).

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

What do we consider as a dynamic sql statement?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

a)我们认为什么是动态sql语句？是否有任何sql语句可以动态地向SQL字符串中添加一个子句，甚至只添加一个子句的一部分？B)那么，对动态提供的值使用占位符的参数化字符串不也被视为动态sql语句吗？谢谢

问我们认为什么是动态sql语句？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我们认为什么是动态sql语句？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我们认为什么是动态sql语句？
EN