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