T-SQL User defined function overloading?

Question

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

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

Solution 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.

Solution 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.

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

Solution 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.

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