SQL 存储过程 EXEC 与 sp_executesql 的区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14722201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored procedure EXEC vs sp_executesql difference?
提问by Registered User
I've written two stored procedure one with sp_executesqland other doesn't have sp_executesql
both are executing properly same results, I didn't get what is the difference here between
我写了两个存储过程，一个sp_executesql和另一个没有 sp_executesql 都正确执行相同的结果，我不明白这里有什么区别
EXEC (@SQL) vs EXEC sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status
EXEC (@SQL) vs EXEC sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status
and How EXEC(@SQL) is prone to SQL injection and sp_executesql @SQL...... isn't?
以及 EXEC(@SQL) 是如何容易发生 SQL 注入的，而 sp_executesql @SQL ......不是吗？
Below Stored Procedure without sp_executesql
下面没有 sp_executesql 的存储过程
ALTER proc USP_GetEmpByStatus
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print (@SQL)
EXEC (@SQL)
END
EXEC USP_GetEmpByStatus 'Active'
Below stored procedure with sp_executesql
下面带有 sp_executesql 的存储过程
create proc USP_GetEmpByStatusWithSpExcute
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'JProCo.dbo.Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print @SQL
exec sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status
END
EXEC USP_GetEmpByStatusWithSpExcute 'Active'
采纳答案by Joachim Isaksson
Your sp_executesqlSQL should probably be;
您的sp_executesqlSQL 应该是；
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' +
@TableName + ' where Status=@eStatus'
This will allow you to call sp_executesql with @eStatus as a parameter instead of embedding it into the SQL. That will give the advantage that @eStatus can contain any charactersand it will be properly escaped automatically by the database if required to be secure.
这将允许您使用@eStatus 作为参数调用 sp_executesql，而不是将其嵌入到 SQL 中。这将提供@eStatus 可以包含任何字符的优势，并且如果需要安全，它将被数据库自动正确转义。
Contrast that to the SQL required for EXEC;
将其与EXEC所需的 SQL 进行对比；
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' +
@TableName + ' where Status=' + char(39) + @Status + char(39)
...where a char(39) embedded in @Status will make your SQL invalid and possibly create an SQL injection possibility. For example, if @Status is set to O'Reilly, your resulting SQL would be;
...在 @Status 中嵌入的 char(39) 将使您的 SQL 无效并可能创建 SQL 注入的可能性。例如，如果 @Status 设置为O'Reilly，则生成的 SQL 将是；
select acol,bcol,ccol FROM myTable WHERE Status='O'Reilly'
回答by FLICKER
Besides the usage, there are some important differences:
除了用法之外，还有一些重要的区别：
sp_executesqlallows for statements to be parameterized Therefore It's more secure thanEXECin terms of SQL injectionsp_executesqlcan leverage cached query plans. The TSQL string is built only one time, after that every time same query is called withsp_executesql, SQL Server retrieves the query plan from cache and reuses itTemp tables created in
EXECcan not use temp table caching mechanism
sp_executesql允许对语句进行参数化 因此它比EXECSQL 注入更安全sp_executesql可以利用缓存的查询计划。TSQL 字符串只构建一次，之后每次使用 调用相同的查询时sp_executesql，SQL Server 从缓存中检索查询计划并重用它创建的
EXEC临时表不能使用临时表缓存机制
回答by JeffB
With sp_executesql, you don't have to build your query like that. You could declare it like this:
使用sp_executesql，您不必像那样构建查询。你可以这样声明：
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' +
@TableName + ' where Status=@eStatus'
This way if your @Statusvalue came from a user you can use @eStatusand not have to worry about escaping '. sp_executesql gives you the ability to put variables in your query in string form, instead of using concatenation. So you have less to worry about.
这样，如果您的@Status价值来自您可以使用的用户@eStatus，而不必担心转义'。sp_executesql 使您能够以字符串形式将变量放入查询中，而不是使用串联。所以你不用担心。
The column and table variables are still the same, but that's less likely to be directly from a user.
列和表变量仍然相同，但不太可能直接来自用户。
回答by Mohammadreza
With ExecYou can't have a place holderin your T-Sql statement string.
使用Exec您的 T-Sql 语句字符串中不能有占位符。
sp_executesqlgives you the advantage of having a place holder and pass the actual value at runtime
sp_executesql为您提供了具有占位符并在运行时传递实际值的优势
