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

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

只是好奇。

150990 次浏览
小开
最佳答案

您可以在脚本开头附近调用 CREATE Function，在脚本结尾附近调用 DROP Function。

小开

Common Table Expressions 允许您定义只在 select、 insert、 update 和 delete 语句范围内持续的基本视图。取决于你需要做什么，它们可能非常有用。

小开

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

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

在 SQL 脚本中，而不是函数中。您可以让 proc 将它的结果存储在一个临时表中，然后在以后的脚本中使用该信息。.

小开

在脚本中，您有更多的选项，并且可以更好地进行理性分解。查看 SQLCMD 模式(SSMS-> Query Menu-> SQLCMD mode) ，特别是: 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。

小开

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

下面是我过去在 MS SQL 中用来实现对 Scalar 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 需求中使用该函数。

小开

对于正在查这个的人来说，这只是另一个想法。始终可以在 temdb 中创建永久函数。该函数不会以 # # 或 # 作为前缀来表示它是一个临时对象。它将“永久”持久化，直到删除它或重新启动服务器并在没有它的情况下重新构建 temdb。关键是，如果您自己的垃圾收集失败，一旦服务器重新启动，它最终将消失。

该函数的作用域将在 TempDB 中，但它可以引用服务器上另一个具有3个部分名称的数据库。(dbname.schema.objectname)或者更好的是，您可以传入函数执行其工作所需的所有参数，这样它就不需要查看其他数据库中的其他对象。


提交答案