我可以在脚本或存储过程中创建一次使用的函数吗?

In SQL Server 2005, is there a concept of a one-time-use, or local function declared inside of a SQL script or Stored Procedure? I'd like to abstract away some complexity in a script I'm writing, but it would require being able to declare a function.

在SQL Server 2005中，是否存在在SQL脚本或存储过程中声明的一次性或本地函数的概念?我想在我正在编写的脚本中抽象出一些复杂性，但是它需要能够声明一个函数。

Just curious. Thanks!!

只是好奇。谢谢! !

6 个解决方案

#1

You can call CREATE Function near the beginning of your script and DROP Function near the end.

您可以在脚本开头附近调用CREATE函数，在末尾处调用DROP函数。

#2

You can create temp stored procedures like:

您可以创建临时存储过程，如:

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

in an SQL script, but not functions. You could have the proc store it's result in a temp table though, then use that information later in the script ..

在SQL脚本中，而不是函数中。您可以使用proc存储，它会导致一个临时表，然后在脚本中稍后使用该信息。

#3

Common Table Expressions let you define what are essentially views that last only within the scope of your select, insert, update and delete statements. Depending on what you need to do they can be terribly useful.

通用表表达式允许您定义那些只在select、insert、update和delete语句范围内有效的视图。取决于你需要做什么，它们可能非常有用。

#4

I know I might get criticized for suggesting dynamic SQL, but sometimes it's a good solution. Just make sure you understand the security implications before you consider this.

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

#5

In scripts you have more options and a better shot at rational decomposition. Look into SQLCMD mode (Query Menu -> SQLCMD mode), specifically the :setvar and :r commands.

在脚本中，您有更多的选项和更好的rational分解的机会。查看SQLCMD模式(查询菜单-> SQLCMD模式)，特别是:setvar和:r命令。

Within a stored procedure your options are very limited. You can't create define a function directly with the body of a procedure. The best you can do is something like this, with dynamic SQL:

在存储过程中，您的选项非常有限。不能使用过程的主体直接创建定义函数。使用动态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

This approximates a global temporary function, if such a thing existed. It's still visible to other users. You could append the @@SPID of your connection to uniqueify the name, but that would then require the rest of the procedure to use dynamic SQL too.

如果存在全局临时函数，则近似为全局临时函数。其他用户仍然可以看到它。您可以附加您的连接的@ @@SPID来惟一化名称，但是这需要过程的其余部分也使用动态SQL。

#6

The below is what I have used i the past to accomplish the need for a Scalar UDF in MS SQL:

下面是我在MS SQL中使用I过去完成标量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

This approach which uses a global variable for the PROCEDURE allows you to make use of the function not only in your scripts, but also in your Dynamic SQL needs.

这种方法为过程使用全局变量，允许您不仅在脚本中，而且在动态SQL需求中使用该函数。

#1