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?

Answer 1

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.

Answer 2

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

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

param1;param2;parma3;param4

or

param1:type;param2:type;param3:type

or

calltype|param1;param2;param3

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

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

Answer 3

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.

Answer 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:

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

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

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!