I want to do create a function which would basically take float or integer values and convert to decimal of particular length and precision.
I have written the code below, but it seems CAST does not allow variable withing DECIMAL function :
CREATE OR ALTER FUNCTION dbo.DECI(@in as sql_variant,@len as int, @prec as int)
returns sql_variant
as
begin
return CAST(@in as decimal(@len,@prec))
end
Implicit casting from SQL_VARIANT to most types (including DECIMAL) is not allowed, so you'll have to do explicit ones in most queries anyways
When you try to use this in (for example) in an INSERT statement
DECLARE @TestTable TABLE
(
DecimalCol DECIMAL(3,2)
);
INSERT INTO @TestTable
(
DecimalCol
)
VALUES
(dbo.DECI(1.1, 3, 2))
You will face with the following error message:
Msg 257, Level 16, State 3, Line 44 Implicit conversion from data type sql_variant to decimal is not allowed. Use the CONVERT function to run this query.
Now, you'll have to update your query to:
INSERT INTO @TestTable
(
DecimalCol
)
VALUES
(CONVERT(DECIMAL(3,2), dbo.DECI(1.1, 3, 2)))
At this point, you did nothing, but a performance killer.
Do your stuff in the query directly:
INSERT INTO @TestTable
(
DecimalCol
)
VALUES
(CONVERT(DECIMAL(3,2), 1.1))
SELECTof multiple rows and columns, even if they vary over time or by the execution parameters.SQL_VARIANTthat you normally can't do anything with without explicit conversion, so you've gained nothing.sql_variantis a "known" type, @EzLo, but using that data type has it own set of... Shall we say "features" related to it.