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?
2
-
2Just to keep this question as a valid duplicate target, this problem has not been solved in SQL Server 2008, 2008 R2 or 2012. In fact the only thing that ever came close - numbered procedures - has been deprecated.Aaron Bertrand– Aaron Bertrand2012-11-27 23:04:27 +00:00Commented Nov 27, 2012 at 23:04
-
3TSQL still has no function overloading as of SQL 2017.David Browne - Microsoft– David Browne - Microsoft2018-07-05 14:52:37 +00:00Commented Jul 5, 2018 at 14:52
Add a comment
|
7 Answers
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.
10 Comments
matao
this isn't "make apples look like oranges", function overloading is a common and elegant solution to the problem of doing a similar operation to different data types. such a shame T-SQL doesn't support it :(
Nick Binnet
I agree with matao. It is one of the most essential features of Object Relational Database Management Systems.
FDavidov
This is an old post but still I found it appropriate to add a comment. SQL Server IS DOING OVERLOAD(!), for instance CONCAT(<text>,<text>), CONCAT(<text>,<number>), CONCAT(<text>,<date>). Now @JohnSaunders, think of a function that would return the highest of a list of values (like in ORACLE's GREATEST function). You would provide, say, 4 numbers, or 5 dates, or 6 strings to the very same function name. Even most basic, think of the "+" operator that accepts integers, floating numbers, strings, etc. So, function overloading exists well before the inventors of OO were born.
FDavidov
@JohnSaunders then, I guess, you would agree with me that it has nothing to do with OBJECTS, right?
Bacon Bits
I think the issue here is that CONCAT() is a variable argument function, which UDFs don't allow you to create. Similarly, CONVERT() has both a 2 argument and 3 argument versions. UDFs don't allow that, either. SQL Server certainly supports this type of function. You just can't make one yourself.
|
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
Comments
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.
Comments
You can pass in an 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 necessary, 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...
Use the technique from the link to split apart this array and use program logic to use those values as you wish.
Comments
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.
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
Comments
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).
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 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 code than overloading
Comments
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!
2 Comments
cjk
This isn't really helpful as the question specifically states MSSQL 2005
Jay D. K.
Just tested against our MSSQL2005 and it seems that it is not possible to overload in MSSQL. Create function count() returns int begin return (0) end; Create function count(@parm int) returns int begin return (@parm) end; [Error Code: 2714, SQL State:42S01] There is already an object named 'count' in the database. I have figured out how to drop overloaded functions in DB2: drop function Schema1.F1 (date); drop function Schema1.F1 (timestamp); Most of our functions look more like this though: drop function Schema1.F1 (varchar(256));