Можно ли создать функцию одноразового использования в скрипте или хранимой процедуре?

в SQL Server 2005 существует ли концепция одноразового использования или локальной функции, объявленной внутри сценария SQL или хранимой процедуры? Я хотел бы абстрагироваться от некоторой сложности в сценарии, который я пишу, но для этого потребуется возможность объявить функцию.

просто любопытно.

6 79
sql sql-server scripting tsql sql-server-2005

6 ответов:

можно назвать CREATE Function в начале вашего скрипта и DROP Function ближе к концу.

53

вы можете создать временные хранимые процедуры, такие как:

create procedure #mytemp as
begin
   select getdate() into #mytemptable;
end

в SQL-скрипте, но не функции. У вас может быть хранилище proc, но это результат во временной таблице, а затем используйте эту информацию позже в сценарии ..

65

Общие Табличные Выражения позвольте вам определить, что такое по существу представления, которые длятся только в рамках ваших инструкций select, insert, update и delete. В зависимости от того, что вам нужно сделать, они могут быть очень полезны.

20

Я знаю, что меня могут критиковать за предложение динамического SQL, но иногда это хорошее решение. Просто убедитесь, что вы понимаете последствия для безопасности, прежде чем рассматривать это.

DECLARE @add_a_b_func nvarchar(4000) = N'SELECT @c = @a + @b;';
DECLARE @add_a_b_parm nvarchar(500) = N'@a int, @b int, @c int OUTPUT';

DECLARE @result int;
EXEC sp_executesql @add_a_b_func, @add_a_b_parm, 2, 3, @c = @result OUTPUT;
PRINT CONVERT(varchar, @result); -- prints '5'
7

в скриптах у вас есть больше возможностей и лучший выстрел в рациональной декомпозиции. Посмотрите в режиме SQLCMD (меню запроса - > режим SQLCMD), в частности :setvar и :r команды.

в рамках хранимой процедуры ваши возможности очень ограничены. Вы не можете создать функцию define непосредственно с телом процедуры. Лучшее, что вы можете сделать, это что-то вроде этого, с динамическим SQL:

create proc DoStuff
as begin

  declare @sql nvarchar(max)

  /*
  define function here, within a string
  note the underscore prefix, a good convention for user-defined temporary objects
  */
  set @sql = '
    create function dbo._object_name_twopart (@object_id int)
    returns nvarchar(517) as
    begin
      return 
        quotename(object_schema_name(@object_id))+N''.''+
        quotename(object_name(@object_id))
    end
  '

  /*
  create the function by executing the string, with a conditional object drop upfront
  */
  if object_id('dbo._object_name_twopart') is not null drop function _object_name_twopart
  exec (@sql)

  /*
  use the function in a query
  */
  select object_id, dbo._object_name_twopart(object_id) 
  from sys.objects
  where type = 'U'

  /*
  clean up
  */
  drop function _object_name_twopart

end
go

это аппроксимирует глобальную временную функцию, если такая вещь существовала. Это по-прежнему виден для других пользователей. Вы можете добавить @@SPID вашего соединения в uniqueify имя, но это потребует, чтобы остальная часть процедуры также использовала динамический SQL.

2

ниже приведено то, что я использовал в прошлом для выполнения потребности в скалярном UDF в MS SQL:

IF OBJECT_ID('tempdb..##fn_Divide') IS NOT NULL DROP PROCEDURE ##fn_Divide
GO
CREATE PROCEDURE ##fn_Divide (@Numerator Real, @Denominator Real) AS
BEGIN
    SELECT Division =
        CASE WHEN @Denominator != 0 AND @Denominator is NOT NULL AND  @Numerator != 0 AND @Numerator is NOT NULL THEN
        @Numerator / @Denominator
        ELSE
            0
        END
    RETURN
END
GO

Exec ##fn_Divide 6,4

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

2