Получение ошибки при выполнении динамического sql внутри функции (SQL Server)?

Я создаю функцию для выполнения динамического SQL и возвращаю значение. Я получаю "Только функции, а некоторые расширенные хранимые процедуры могут выполняться изнутри функции". как ошибка.

Функция:

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

Выполнение:

Select dbo.fn_GetPrePopValue('10002618') from Questions Where QuestionID=114

Select fn_GetPrePopValue('10002618') from Questions Where QuestionID=114

Правильно ли вызвана функция или неправильная функция?

Ответ 1

Вы не можете использовать динамический 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

Функции ограничены в том, что они могут использовать, так что вы можете использовать их в запросе без случайного создания чего-то, что дало бы ужасную производительность. Использование динамических запросов - одна из тех вещей, поскольку это может вызвать планирование запросов для каждого выполнения, а также не позволит функции быть частью плана запроса.

В этом случае вам вообще не нужен динамический запрос, просто верните значение:

Create Function fn_GetPrePopValue(@paramterValue nvarchar(100))
returns int as
begin

return (select Grant_Nr From Grant_Master where grant_id = @paramterValue)

end

Ответ 3

Я не думаю, что вы можете использовать динамический 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 [email protected]

  return @value
end

SQL Fiddle Demo

Кроме того, проверьте свои типы данных, чтобы убедиться, что у вас поля правильные. Кажется странным передавать в varchar для id и возвращать int для другого поля. В любом случае, это поможет вам двигаться в правильном направлении.