CODEWITHSUNDEEP
sql-server
Zabi Sidiqkhil
Zabi Sidiqkhil

Reputation: 154

SQL Server: Currency Converter Function

The function I have created which will convert any currency to £ POUND Sterling. Indeed any currency is limited to what is defined in the function.

The function is created fine but when I am using it in a select statement on a field to give me a rate based on wither the region is UK/UAE/JAP etc

I get the following error:

Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting int to data type numeric.

The logic is fine, how do I get through the data type please?

Select statement to use the function

SELECT RegionCode
      ,VALUE
     ,[dbo].[fnCurrencyConvertee](RegionCode) * VALUE
  FROM temp.dbo.Treasurey

Function code --IMPORTANT NOTE, THE FOLLOWING CODE IS NOW CORRECT AND WORKS AWESOME, THANKS TO EVERYONE'S CONTRIBUTION

USE [temp]
GO

CREATE FUNCTION [dbo].[zsCurrencyConvertee] 
     (@inPutRegionCode_column VARCHAR(5))
RETURNS DECIMAL(38,2)
AS
BEGIN
    DECLARE @Convert DECIMAL(20,2)
    DECLARE @Default DECIMAL(20,2) --Default currency is £,

    DECLARE @UK DECIMAL(20,2)
    DECLARE @TURK DECIMAL(20,2) --TURKEY
    DECLARE @AFG DECIMAL(20,2)
    DECLARE @MEX DECIMAL(20,2) --MEXICO
    DECLARE @MOR DECIMAL(20,2) --MOROCCO
    DECLARE @UAE DECIMAL(20,2)
    DECLARE @IRA DECIMAL(38,2) --IRAQ
    DECLARE @JAP DECIMAL(20,2)
    DECLARE @CHI DECIMAL(20,2)

    SET @Convert = 0.00 --Variable which will be reused to hold converted values
    SET @Default = 1.00 --Reset this value if the conversion is required between other currencies such as UAE to JAPANESE etc

    SET @UK = 1
    SET @TURK = 6.87
    SET @AFG = 97.58
    SET @MEX = 24.69
    SET @MOR = 12.31
    SET @UAE = 4.73
    SET @IRA = 1536.43
    SET @JAP = 141.15
    SET @CHI = 8.75

    BEGIN
            IF(@inPutRegionCode_column = 'UK')
            BEGIN
                SET @Convert = @Default / @UK
            END
            ELSE IF(@inPutRegionCode_column = 'TURK')
            BEGIN
                SET @Convert = @Default / @TURK
            END
            ELSE IF(@inPutRegionCode_column = 'AFG')
            BEGIN
                SET @Convert = @Default / @AFG
            END
            ELSE IF(@inPutRegionCode_column = 'MEX')
            BEGIN
                SET @Convert = @Default / @MEX
            END
            ELSE IF(@inPutRegionCode_column = 'MOR')
            BEGIN
                SET @Convert = @Default / @MOR
            END
            ELSE IF(@inPutRegionCode_column = 'UAE')
            BEGIN
                SET @Convert = @Default / @UAE
            END
            ELSE IF(@inPutRegionCode_column = 'IRA')
            BEGIN
                SET @Convert = @Default / @IRA
            END
            ELSE IF(@inPutRegionCode_column = 'JAP')
            BEGIN
                SET @Convert = @Default / @JAP
            END
            ELSE IF(@inPutRegionCode_column = 'CHI')
            BEGIN
                SET @Convert = @Default / @CHI
            END
    END
RETURN
    @Convert
END

]

Table if you may need it

USE [temp]
GO
CREATE TABLE Treasurey
(
    RegionCode VARCHAR(5),
    Value INT
)

INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'TURK', 23)
INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'AFG', 55)
INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'UK', 33)
INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'MOR', 100)
INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'UAE', 150)
INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'IRA', 200)
INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'JAP', 23)
INSERT [dbo].[Treasurey] ([RegionCode], [Value]) VALUES (N'CHI', 49)

Upvotes: 3

Views: 2058

Answers (1)

S3S
S3S

Reputation: 25132

You are using decimal (2,2) which means the largest number you can fit in there is 0.99. However, you are trying to put larger number in. 

DECLARE @IRA DECIMAL(2,2) --IRAQ
SET @IRA = 1536.43

Make your variable declarations larger. At least decimal(6,2) based on this sample data... but you may need to go much larger decimal(38,2) for example to account for lager numeric ranges.

DECLARE @IRA DECIMAL(6,2) --IRAQ
SET @IRA = 1536.43

See more on precision, scale, and length.

Upvotes: 3

Related Questions