[ACCEPTED]-T-SQL User defined function overloading?-tsql
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.
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.
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
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
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
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:
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.
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
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
More Related questions