存储过程EXEC和sp_executesql有什么区别？

Question

我编写了两个存储过程，其中一个使用了sp_executesql，另一个没有使用。 两者都能够正常执行，结果相同。我不明白它们之间的区别在哪里。

EXEC (@SQL)和EXEC sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status之间有何区别？而EXEC(@SQL)有什么容易受到SQL注入的问题，而sp_executesql @SQL……则没有？

下面是一个没有使用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'

以下是使用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'

- Registered User

---

这回答了你的问题吗？动态SQL - EXEC(@SQL)与EXEC SP_EXECUTESQL(@SQL) - Michael Freidgeim

Answer 1

你的 SQL 中的 sp_executesql 可能应该是以下这样的：

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=@eStatus'

这将允许您使用@eStatus作为参数调用sp_executesql，而不是将其嵌入到SQL语句中。这样可以获得一个优势，即@eStatus可以包含任何字符，并且如果需要进行安全转义，数据库会自动正确转义它。

相比之下，使用EXEC所需的SQL则有所不同；

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=' + char(39) + @Status + char(39)

...如果在@Status中嵌入char(39)，将会使你的SQL无效，并可能创建SQL注入漏洞。例如，如果将@Status设置为O'Reilly，则生成的SQL将是;

select acol,bcol,ccol FROM myTable WHERE Status='O'Reilly'

- Joachim Isaksson

---

为什么where子句没有用' '括起来？ - Registered User

如果将@eStatus用引号包裹起来，它将使用字符串“@eStatus”在数据库中进行搜索，如果使用未加引号的@eStatus，则将使用其值。例如，如果@eStatus是“test”，则INSERT INTO myTable VALUES (@eStatus)将插入字符串“test”，而INSERT INTO myTable VALUES ('@eStatus')将插入字符串“@eStatus”。 - Joachim Isaksson

我认为第一个代码示例缺少一个闭合的 '。 - Sam

另一个区别是，当您在动态SQL中使用临时表时，sp_executesql使用缓存计划，而exec不使用它。 - Sankara

Answer 2

使用sp_executesql，您不必像那样构建查询。您可以这样声明它：

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
@TableName + ' where Status=@eStatus'

如果你的@Status值来自用户，你可以使用@eStatus而不必担心转义'。使用sp_executesql可以让你在查询中以字符串形式放置变量，而不是使用连接符。这样你就会少些烦恼。

列和表变量仍然是相同的，但这很少直接来自用户。

- JeffB

Answer 3

使用Exec在T-Sql语句字符串中不能有占位符。

sp_executesql允许在运行时使用占位符，并传递实际值。

- Mohammadreza