Tutorial :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 ...


Solution: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 :)


Solution: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...


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »