如何执行作为 sp 参数传递的 sql 文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2285872/
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
How do I execute sql text passed as an sp parameter?
提问by Dane O'Connor
I have a stored procedure with an nvarchar parameter. I expect callers to supply the text for a sql command when using this SP.
我有一个带有 nvarchar 参数的存储过程。我希望调用者在使用此 SP 时为 sql 命令提供文本。
How do I execute the supplied sql command from within the SP?
如何从 SP 中执行提供的 sql 命令？
Is this even possible?-
这甚至可能吗？ -
I thought it was possible using EXEC but the following:
我认为可以使用 EXEC 但以下内容：
EXEC @script
errors indicating it can't find a stored procedure by the given name. Since it's a script this is obviously accurate, but leads me to think it's not working as expected.
错误表明它找不到给定名称的存储过程。由于它是一个脚本，这显然是准确的，但让我认为它没有按预期工作。
回答by OMG Ponies
Use:
用：
BEGIN
EXEC sp_executesql @nvarchar_parameter
END
...assuming the parameter is an entire SQL query. If not:
...假设参数是一个完整的 SQL 查询。如果不：
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT ...' + @nvarchar_parameter
BEGIN
EXEC sp_executesql @SQL
END
Be aware of SQL Injection attacks, and I highly recommend reading The curse and blessing of Dynamic SQL.
注意SQL 注入攻击，我强烈推荐阅读动态 SQL 的诅咒和祝福。
回答by Josh
回答by AaronLS
You use EXECUTEpassing it the command as a string. Note this could open your system up to serious vulnerabilities given that it is difficult to verify the non-maliciousness of the SQL statements you are blindly executing.
您使用EXECUTE将命令作为字符串传递给它。请注意，鉴于很难验证您盲目执行的 SQL 语句的非恶意性，这可能会使您的系统面临严重漏洞。
回答by Joel Coehoorn
How do I execute the supplied sql command from within the SP?
如何从 SP 中执行提供的 sql 命令？
Very carefully. That code could do anything, including add or delete records, or even whole tables or databases.
非常小心。该代码可以做任何事情，包括添加或删除记录，甚至整个表或数据库。
To be safe about this, you need to create a separate user account that only has dbreader permissions on just a small set of allowed tables/views and use the EXECUTE AScommand to limit the context to that user.
为了安全起见，您需要创建一个单独的用户帐户，该帐户仅对一小组允许的表/视图具有 dbreader 权限，并使用EXECUTE AS命令将上下文限制为该用户。
