overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob