T-SQL用户定义函数重载?

I understand that T-SQL is not object oriented. I need to write a set of functions that mimics method overloading in C#.

我理解T-SQL不是面向对象的。我需要编写一组函数来模拟c#中的方法重载。

Is function overloading supported in T-SQL in any way? If there is a hack to do this, is it recommended?

T-SQL中是否支持函数重载?如果有这样的技巧，是否推荐?

7 个解决方案

#1

No, there is no way to do this.

不，这是不可能的。

I recommend you revisit the requirement, as "make apples look like oranges" is often difficult to do, and of questionable value.

我建议您重新考虑这一要求，因为“让苹果看起来像桔子”通常很难做到，而且价值可疑。

#2

One thing I have done successfully is to write the function in such a way as to allow it to handle null values, and then call it with nulls in place of the parameters you would like to omit.

我已经成功地完成了一件事，即以允许它处理空值的方式编写函数，然后使用nulls代替您想要省略的参数。

Example:

例子:

create function ActiveUsers
(
    @departmentId int,
    @programId int
)
returns int
as
begin
    declare @count int

    select @count = count(*)
    from users
    where
        departmentId = isnull(@departmentId, departmentId)
        and programId = isnull(@programId, programId)

    return @count
end
go

Uses:

用途:

select ActiveUsers(1,3) -- users in department 1 and program 3
select ActiveUsers(null,3) -- all users in program 3, regardless of department
select ActiveUsers(null,null) -- all users

#3

You could pass in a sql_variant, but it comes with all sorts of hazards around it; you can't really use strong typing like you can with OO languages and overloading.

您可以传入sql_variant，但它附带各种各样的危险;不能像使用面向对象语言和重载那样使用强类型。

If you need to find the base type within your function, you can use the SQL_VARIANT_PROPERTY function.

如果需要在函数中找到基本类型，可以使用SQL_VARIANT_PROPERTY函数。

#4

You can pass in an array of values within a single string and parse them out using this techique by Erland Sommarskog.

您可以在一个字符串中传递一个值数组，并使用Erland Sommarskog的techique解析它们。

Create a function with a varchar(max) parameter or several if necessary, then have your parameter values in that string like:

创建一个带有varchar(max)参数或多个参数(如果需要的话)的函数，然后将参数值放在该字符串中，如:

param1;param2;parma3;param4

或

param1:type;param2:type;param3:type

或

calltype|param1;param2;param3

etc, you are only limited by your imagination...

等等，你只是被你的想象力所限制……

Use the technique from the link to split apart this array and use program logic to use those values as you wish.

使用链接中的技术来分割这个数组，并使用程序逻辑来根据需要使用这些值。

#5

One solution would be to utilize the sql_variant data type. This example works as long as you use the same datatype for both values. Returns whatever datatype you send it.

一种解决方案是使用sql_variant数据类型。只要对两个值使用相同的数据类型，这个示例就可以工作。返回您发送的任何数据类型。

create function dbo.Greater(
@val1 sql_variant
,@val2 sql_variant
) returns sql_variant
as
begin
declare @rV sql_variant

set @rV = case when @val1 >= @val2 then @val1
               else @val2 end

return @rV
end
go

#6

A solution I've had some luck with is either creating a number of functions that each takes a different data type - or casting all input to NVARCHAR(MAX).

我很幸运的一个解决方案是创建一些函数，每个函数都具有不同的数据类型——或者将所有输入转换为NVARCHAR(MAX)。

1. creating a number of functions that each takes a different data type

1。创建多个函数，每个函数使用不同的数据类型

CREATE FUNCTION [dbo].[FunctionNameDatetime2]
CREATE FUNCTION [dbo].[FunctionNameInt]
CREATE FUNCTION [dbo].[FunctionNameString] --(this is not a typo)
CREATE FUNCTION [dbo].[FunctionNameUniqueidentifier]
...

Problem: duplication of code, and a lot functions

问题:重复的代码，和许多功能

2. Cast all input to NVARCHAR(MAX)

2。将所有输入转换为NVARCHAR(MAX)

CREATE FUNCTION [dbo].[IntToNvarchar]
(
    @Key INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN ISNULL(CAST(@Key AS NVARCHAR), '');
END

CREATE FUNCTION [dbo].[FunctionName]
(
    @Key NVARCHAR(MAX)
)
RETURNS CHAR(32)
AS
BEGIN
    DECLARE @something CHAR(32)

    do stuff ...

    RETURN @something;
END

SELECT [dbo].[FunctionName]([dbo].[IntToNvarchar](25))

Problem: less elegant code than overloading

问题:代码不如重载优雅

#7

-4

I overload Functions all the time, but I happen to know that these kind of issues are often highly dependent on platform.

我总是重载函数，但我碰巧知道这类问题通常高度依赖于平台。

On our DB2 system, I routinely overload like the following:

在我们的DB2系统上，我经常超负荷工作，如下所示:

CREATE Function Schema1.F1 (parm date) returns date return date + 1month;

创建函数代码基于Schema1中。F1 (parm date)返回日期+ 1个月;

CREATE Function Schema1.F1 (parm timestamp) returns date return date(timestamp) + 1month;

创建函数代码基于Schema1中。F1 (parm时间戳)返回日期返回日期(时间戳)+ 1个月;

This is actually quite useful when you have multiple queries which have similar formating requirements.

当您有多个具有类似形式需求的查询时，这实际上非常有用。

The only problem I have found about this so far, is you better be sure that you want the function because the standard drop function "schema"."name" fails because it cannot determine which function to drop. If anyone knows how to drop overloaded sql functions, let me know!

到目前为止，我发现的唯一问题是，您最好确保您想要这个函数，因为标准的drop函数“schema”。名称“失败，因为它不能确定要删除哪个函数。如果有人知道如何删除重载的sql函数，请告诉我!

#1