December 20, 2007 at 10:01 am
I'm having a problem with one of my user defined functions. Specifically I'm trying to use some passed in values to set the scale and precision for a numeric data type... When I try and do this the syntax pukes on me, following is my code...
CREATE FUNCTION [dbo].[fn_int_to_numeric]
(
@origInt as int, /* int to be converted */
@prec as int,
@scale as int
)
RETURNS numeric(@scale, @prec)
AS
/**************************************************************************************************************
** Function: This function converts currency that has been stored as an int to a numeric
with the user-specified precision and a scale of 2. (e.g. $25.00 is stored as an int as
'2500'. This function converts it to '25.00'). It requires the int to be converted and the
size of the numeric value to be returned.
****************************************************************************************************************/
BEGIN
DECLARE
@rightmost varchar(2),
@leftmost varchar(8),
@tempStr varchar(10),
@length int
SET @length = (len(@origInt) -@scale)
DECLARE @newNum numeric(@scale, @prec)
IF (@origInt > 0)
BEGIN
SET @rightmost = right (@origInt, @scale)
SET @leftmost = left(@origInt, @length)
SET @tempStr = (@leftmost + '.' + @rightmost)
SET @newNum = cast(@tempStr as numeric(@scale, @prec))
END
ELSE
SET @newNum = (0.00)
RETURN @newNum
END
December 20, 2007 at 4:03 pm
I don't understand why you need to craft a decimal this way. Why not just decide on the maximum precision and scale you need based on the expected data input and use that?
December 21, 2007 at 4:55 am
Is there a reason that the money or smallmoney datatypes aren't good enough for your purposes? Please correct me if I'm wrong, but the money datatypes are "fixed point" numbers out to 4 decimal places. Perhaps your "user defined precision" is a requirement to round to certain decimal places? This can be handled with most client side formatting.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply