[ACCEPTED]-T-SQL User defined function overloading?-tsql

Accepted answer

Score: 15

No, there is no way to do this.

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

Score: 10

One thing I have done successfully is to 4 write the function in such a way as to allow 3 it to handle null values, and then call 2 it with nulls in place of the parameters 1 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

Score: 5

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

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

Score: 1

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

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

param1;param2;parma3;param4

param1:type;param2:type;param3:type

calltype|param1;param2;param3

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

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

Score: 0

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

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

Score: 0

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

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

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

Problem: duplication 2 of code, and a lot functions

2. Cast all input to 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 1 code than overloading

Source: stackoverflow.com

[ACCEPTED]-T-SQL User defined function overloading?-tsql

IF/ELSE Stored Procedure

Conditional Operator in SQL Where Clause

SQL Server 2005 Get First and Last date for any Month in any Year

How to create sequential number column index on table with data

retrieve the most recent record for each customer

Why can't I perform an aggregate function on an expression containing an aggregate but I can do so by creating a new select statement around it?

MS SQL Server: Check to see if a user can execute a stored procedure

Which is better: Bookmark/Key Lookup or Index Scan

Create a global static variable in SQL Server?

Eliminate and reduce overlapping date ranges