在函数 (SQL Server) 中执行动态 sql 时出错？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15180561/
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
Getting an error when executing a dynamic sql within a function (SQL Server)?
提问by Chaka
I create a function to execute dynamic SQL and return a value. I am getting "Only functions and some extended stored procedures can be executed from within a function." as an error.
我创建了一个函数来执行动态 SQL 并返回一个值。我收到“只能从函数内部执行函数和一些扩展存储过程”。作为错误。
The function:
功能：
Create Function fn_GetPrePopValue(@paramterValue nvarchar(100))
returns int as
begin
declare @value nvarchar(500);
Set @SQLString = 'Select Grant_Nr From Grant_Master where grant_id=' + @paramterValue
exec sp_executesql
@query = @SQLString,
@value = @value output
return @value
end
The execution:
执行：
Select dbo.fn_GetPrePopValue('10002618') from Questions Where QuestionID=114
and:
和：
Select fn_GetPrePopValue('10002618') from Questions Where QuestionID=114
Is the function being called properly or is the function incorrect?
函数是否被正确调用或函数不正确？
采纳答案by Aleksandr Fedorenko
You cannot use dynamic SQL from a function, neither can you call stored procedures.
您不能从函数中使用动态 SQL，也不能调用存储过程。
Create proc GetPrePopValue(@paramterValue nvarchar(100))
as
begin
declare @value nvarchar(500),
@SQLString nvarchar(4000)
Set @SQLString = 'Select @value = Grant_Nr From Grant_Master where grant_id = @paramterValue'
exec sp_executesql @SQLString, N'@paramterValue nvarchar(100)',
@paramterValue,
@value = @value output
return @value
end
回答by Guffa
Functions are limited in what they can use, so that you can use them in a query without accidentally make something that would give horrible performance. Using dynamic queries is one of those things, as that would cause a query planning for each execution, and also would keep the function from being able to be part of a query plan.
函数可以使用的内容是有限的，因此您可以在查询中使用它们而不会意外地做出会带来糟糕性能的东西。使用动态查询就是其中之一，因为这会导致每次执行的查询计划，并且还会使函数无法成为查询计划的一部分。
You don't need the dynamic query at all in this case, just return the value:
在这种情况下，您根本不需要动态查询，只需返回值：
Create Function fn_GetPrePopValue(@paramterValue nvarchar(100))
returns int as
begin
return (select Grant_Nr From Grant_Master where grant_id = @paramterValue)
end
回答by sgeddes
I don't think you can use dynamic SQL from a function, nor do I think you need to in your case. Looks like you want something closer to this:
我认为您不能从函数中使用动态 SQL，我认为您也不需要。看起来你想要更接近这个的东西：
Create Function dbo.fn_GetPrePopValue(@paramterValue nvarchar(100))
returns int as
begin
declare @value int
declare @SQLString varchar(MAX)
Select @value=Grant_Nr From Grant_Master where grant_id=@paramterValue
return @value
end
Also, please check your data types to make sure you're fields are correct. Seems odd to pass in a varchar for the id and return an int for the other field. Either way, this should help you get going in the right direction.
另外，请检查您的数据类型以确保您的字段正确。为 id 传入 varchar 并为另一个字段返回 int 似乎很奇怪。无论哪种方式，这都应该有助于您朝着正确的方向前进。
