t-sql udf, get the data type of a parameter

Question

is it possible to get a numeric parameter to my udf and do stuff according to its type, like:

if type of @p1 is decimal(10,3) ... else if type of @p1 is decimal(15,3) ... else if type of @p1 is integer ...

Answer 1

Try out the sql_variant_property function...

Some examples...

Declare @Param Int
Set @Param = 30
Select sql_variant_property(@Param, 'BaseType')
Select sql_variant_property(@Param, 'Precision')
Select sql_variant_property(@Param, 'Scale')

Declare @ParamTwo float
Set @ParamTwo = 30.53
Select sql_variant_property(@ParamTwo, 'BaseType')
Select sql_variant_property(@ParamTwo, 'Precision')
Select sql_variant_property(@ParamTwo, 'Scale')

Hope it helps :)

Answer 2

You should know what type is declared as in the function parameters.

The udf parameters and return type are explicitly declared as "int" or "decimal(p,s)" etc... no need to work it out...