CODEWITHSUNDEEP
sqlsql-server
Zabi Sidiqkhil
Zabi Sidiqkhil

Reputation: 154

SQL Server create function

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.

Upvotes: 1

Views: 103

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 12959

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

Upvotes: 2

Related Questions