SQL Server 是否可以在脚本或存储过程中创建一次性函数?

carvr3hs  于 2022-11-21  发布在  其他
关注(0)|答案(7)|浏览(139)

在SQL Server 2005中,是否存在在SQL脚本或存储过程中声明的一次性函数或本地函数的概念?我希望在编写的脚本中抽象出一些复杂性,但这需要能够声明函数。
只是好奇而已。

von4xj4u

von4xj4u1#

您可以建立缓存存储程序,例如:

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

您可以让proc将其结果存储在一个临时表中,然后在脚本中使用该信息。

mznpcxlj

mznpcxlj2#

您可以在接近脚本开头时调用CREATE Function,在接近结尾时调用DROP Function

ej83mcc0

ej83mcc03#

Common Table Expressions让你定义什么是视图,它只在你的select,insert,update和delete语句的作用范围内持续。

htrmnn0y

htrmnn0y4#

我知道我可能会因为建议使用动态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'
o4hqfura

o4hqfura5#

下面是我过去在MS SQL中实现标量UDF所用的方法:

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

这种为PROCEDURE使用全局变量的方法使您不仅可以在脚本中使用该函数,还可以在动态SQL需求中使用该函数。

cfh9epnr

cfh9epnr6#

在脚本中,你有更多的选择,更好地进行有理分解。查看SQLCMD模式(SSMS -〉Query Menu -〉SQLCMD模式),特别是:setvar和:r命令。
在一个存储过程中,你的选择非常有限。你不能直接用过程的主体来创建和定义一个函数。你能做的最好的事情是这样的,用动态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来唯一化名称,但这将要求过程的其余部分也使用动态SQL。

zmeyuzjn

zmeyuzjn7#

这是另一个建议。您可以在tempdb中创建一个永久函数。该函数不会以##或#作为前缀来表示它是一个临时对象。它将“永久”保留,直到删除它或重新启动服务器并在没有它的情况下重建tempdb。关键是,如果您自己的垃圾收集失败,那么一旦服务器重新启动,它最终会消失。
函数的作用域在TempDB内,但它可以引用服务器上具有3个部分名称的另一个数据库。(dbname.schema.objectname),或者更好的是,您可以传入函数执行其工作所需的所有参数,这样它就不需要查看其他数据库中的其他对象。

相关问题