Can someone translate my error to English please:
Overload resolution failed because no accessible 'Round' can be called without a narrowing conversion:
You may need to explicitly convert the expression result into a numeric data type. For instance, CDbl() would convert into a double.
E.g.: =Round( CDbl( .....) )
-- Robert
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
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
