如何执行作为sp参数传递的sql文本? [英] How do I execute sql text passed as an sp parameter?
问题描述
我有一个带有nvarchar参数的存储过程。我希望调用者在使用此SP时提供sql命令的文本。
I have a stored procedure with an nvarchar parameter. I expect callers to supply the text for a sql command when using this SP.
如何从SP中执行提供的sql命令?
How do I execute the supplied sql command from within the SP?
这是否有可能?-
我认为可以使用EXEC,但以下内容:
I thought it was possible using EXEC but the following:
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.
推荐答案
使用:
BEGIN
EXEC sp_executesql @nvarchar_parameter
END
...假设参数是一个完整的SQL查询。如果不是这样的话:
...assuming the parameter is an entire SQL query. If not:
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT ...' + @nvarchar_parameter
BEGIN
EXEC sp_executesql @SQL
END
请注意 SQL注入攻击,我强烈建议阅读动态SQL的诅咒和祝福。
Be aware of SQL Injection attacks, and I highly recommend reading The curse and blessing of Dynamic SQL.
这篇关于如何执行作为sp参数传递的sql文本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!