在函数(sql Server)中执行动态sql时，会出现错误吗?

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?

函数是否被正确调用?函数是否不正确?

3 个解决方案

#1

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

#2

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

#3

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，我也不认为你需要从你的例子中使用动态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

SQL Fiddle Demo

SQL小提琴演示

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看起来很奇怪。不管怎样，这都能帮助你朝着正确的方向前进。

#1