存储过程SQL SELECT语句问题
我在 Windows Server 2008 Enterprise 上使用 SQL Server 2008 Enterprise。在存储过程中，我们可以直接执行SELECT语句。它也可以用这种新的方式执行，我想知道哪种方法更好，为什么？
新方法，
declare @teststatement varchar(500)
set @teststatement = 'SELECT * from sometable'
print @teststatement
exec (@teststatement)
传统方法，
SELECT * from sometable
问候， 乔治
I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. In a stored procedure, we can execute a SELECT statement directly. And it could also be executed in this new way, I am wondering which method is better, and why?
New method,
declare @teststatement varchar(500)
set @teststatement = 'SELECT * from sometable'
print @teststatement
exec (@teststatement)
Traditional method,
SELECT * from sometable
regards,
George
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
仅供参考：这不是一种新方法，它被称为动态 SQL。
当我们需要将某些值设置或连接到 SQL 语句中时，动态 SQL 是首选。
建议使用传统或普通方式的sql语句，因为要遵守存储过程。 首次运行时已遵守 "存储过程在首次运行时编译"
，语句的执行计划是在编译时创建的。
创建执行计划时动态 sql 会被忽略，因为它被视为字符串（声明的 VARCHAR 或 NVARCHAR）。
有关动态查询和存储过程的更多详细信息，请参阅以下文章
动态 SQL 简介第 1 部分
动态 SQL 第 2 部分简介
您想了解的有关存储过程的所有信息
FYI: it’s not a new method, it is known as Dynamic SQL.
Dynamic SQL are preferred when we need to set or concatenate certain values into sql statements.
Traditional or normal way sql statements are recommended, because stored procedures are complied. Complied on first run "Stored Procedure are Compiled on First Run"
, execution plan of statements are being created at the time of compilation.
Dynamic sqls are ignored while creating execution plans, because it is taken as string (VARCHAR or NVARCHAR as declared).
Refer following articles for more details about dynamic query and stored procs
Introduction to Dynamic SQL Part 1
Introduction to Dynamic SQL Part 2
Everything you wanted to know about Stored Procedures
传统方法更安全，因为在保存查询时会对其进行解析。 “exec”方法中的查询不会被解析，并且可能包含错误。
The traditional method is safer, because the query is parsed when you save it. The query in the 'exec' method is not parsed and can contain errors.
如前所述，“新”方式与 SQL 2008 无关。 EXEC 已经存在相当长一段时间了。在大多数情况下，这也是一个非常糟糕的主意。
您失去了参数化 - 这意味着您现在很容易受到SQL 注入的攻击。它很丑陋而且容易出错。效率比较低。它创建了一个新的执行范围 - 这意味着它不能共享变量、临时表等 - 从它调用存储过程。
sp_executesql 是执行动态 SQL 的另一种（也是首选）方法。这是您的客户端应用程序使用的内容，并且它支持参数 - 这解决了 EXEC 最明显的问题。然而，它在存储过程中的用例也非常有限。唯一可弥补的用途是当您需要动态表或列名时。 T-SQL 不支持变量 - 因此您需要使用 sp_executesql。您需要或应该执行的次数非常低。
最重要的是——你最好忘记你曾经听说过它。
The "new" way, as mentioned, has nothing to do with SQL 2008. EXEC has been available for quite some time. It's also - in most cases - a Very Bad Idea.
You lose parameterization - meaning you are now vulnerable to SQL Injection. It's ugly and error-prone. It's less efficient. And it creates a new execution scope - meaning it can't share variables, temp tables, etc. - from it's calling stored proc.
sp_executesql is another (and preferred) method of executing dynamic SQL. It's what your client apps use, and it supports parameters - which fixes the most glaring problem of EXEC. However, it too has very limited use cases within a stored proc. About the only redeeming use is when you need a dynamic table or column name. T-SQL does not support a variable for that - so you need to use sp_executesql. The number of times you need or should be doing that are very low.
Bottom line - you'd be best off forgetting you ever heard of it.