SQL 我可以在脚本或存储过程中创建一次性使用函数吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/981451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I create a One-Time-Use Function in a Script or Stored Procedure?
提问by Mark Carpenter
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.
只是好奇。
采纳答案by Joel Coehoorn
You can call CREATE Functionnear the beginning of your script and DROP Functionnear the end.
您可以CREATE Function在脚本开头和DROP Function结尾附近调用。
回答by Ron Savage
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 将其结果存储在临时表中，然后稍后在脚本中使用该信息..
回答by Welbog
Common Table Expressionslet 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.
通用表表达式让您可以定义哪些本质上是仅在您的选择、插入、更新和删除语句范围内持续的视图。根据您需要做什么，它们可能非常有用。
回答by Tmdean
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'
回答by Peter Radocchia
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.
在脚本中，您有更多选择，并且可以更好地进行合理分解。查看 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。
回答by Gregory Hart
The below is what I have used i the past to accomplish the need for a Scalar UDF in MS SQL:
以下是我过去用来完成 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
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.
这种为 PROCEDURE 使用全局变量的方法使您不仅可以在脚本中使用该函数，还可以在动态 SQL 需求中使用该函数。
