SQL Server create function

Question

I have done the IF statement and am prepared to return the value based on the conditions provided. I tried RETURN CASE too and I receive the exact same error

The intention is to convert currency from £ to $ or EURO, it does the multiplication and returns.

I will polish the conversation latter but help make it return the value please.

If you look near the end of my query you will see RETURN @Converted, although it looks fine here but it gives the following error

[ Msg 178, Level 15, State 1, Procedure fnCurrencyConverter, Line 23 A RETURN statement with a return value cannot be used in this context. ]

Thanks

USE temp
GO

CREATE FUNCTION dbo.fnCurrencyConverter (@inPutMoneey INT, @inPutRegionCode VARCHAR(5))
RETURNS @Return TABLE
(
    Monee MONEY, 
    RegionCode CHAR(5)
)
AS
BEGIN

DECLARE @Converted MONEY

SET @Converted = 0

    BEGIN
        IF(@inPutRegionCode = 'US')
            SET @Converted = @inPutMoneey * 1.29
        ELSE IF(@inPutRegionCode = 'EU')
            SET @Converted = @inPutMoneey * 1.13
        ELSE IF(@inPutRegionCode = 'AFG')
            SET @Converted = @inPutMoneey * 0.50
    END
RETURN 
    @Converted
END

If region code is US then multiply the value by 1.29 which is the current POUND to DOLLAR rate and so on.

Your function is declared as returning a table but it returns MONEY? — nicomp
– nicomp, Commented Jan 21, 2019 at 5:11

Venkataraman R · Accepted Answer · 2019-01-21 05:12:25Z

2

No need to mention table variable name. Just mention RETURN. that is enough. You are not populating the table properly.

USE tempdb
GO

CREATE FUNCTION dbo.fnCurrencyConverter (@inPutMoneey INT, @inPutRegionCode VARCHAR(5))
RETURNS @Return TABLE
(
    Monee MONEY, 
    RegionCode CHAR(5)
)
AS
BEGIN

DECLARE @Converted MONEY

SET @Converted = 0

    BEGIN
        IF(@inPutRegionCode = 'US')
        BEGIN
            SET @Converted = @inPutMoneey * 1.29
            INSERT INTO @Return VALUES (@Converted, 'US')
        END 
        ELSE IF(@inPutRegionCode = 'EU')
        BEGIN
            SET @Converted = @inPutMoneey * 1.13
            INSERT INTO @Return VALUES (@Converted, 'EU')
        END
        ELSE IF(@inPutRegionCode = 'AFG')
        BEGIN
            SET @Converted = @inPutMoneey * 0.50
            INSERT INTO @Return VALUES (@Converted, 'AFG')
        END
    END
RETURN     
END

answered Jan 21, 2019 at 5:12

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zabi Sidiqkhil Over a year ago

Thank you so much, my query was coded exactly as urs but missing INSERT statement. i kept changing it but made no difference got a little frustrated lol. I created this function to struggle on purpose, i will comment on here if I have any further queries about this. thanks a lot

Venkataraman R Over a year ago

Glad that it helped

Shushil Bohara · Accepted Answer · 2019-01-21 05:28:48Z

1

You can use single insert at last because at a time one condition would be true and executed

CREATE FUNCTION dbo.fnCurrencyConverter (@inPutMoneey INT, @inPutRegionCode VARCHAR(5))
RETURNS @Return TABLE
(
    Monee MONEY, 
    RegionCode CHAR(5)
)
AS
BEGIN

DECLARE @Converted MONEY

SET @Converted = 0

    BEGIN
        IF(@inPutRegionCode = 'US')
            SET @Converted = @inPutMoneey * 1.29
        ELSE IF(@inPutRegionCode = 'EU')
            SET @Converted = @inPutMoneey * 1.13
        ELSE IF(@inPutRegionCode = 'AFG')
            SET @Converted = @inPutMoneey * 0.50
    END

    INSERT INTO @Return VALUES(@Converted, @inPutRegionCode)
    --You can also use variable for Region code
    RETURN
END

edited Jan 21, 2019 at 5:28

answered Jan 21, 2019 at 5:19

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

1 Comment

Zabi Sidiqkhil Over a year ago

Thanks a lot, it will reduce coding by a big margin

Collectives™ on Stack Overflow

SQL Server create function

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related