Function with two inputs returns null value-CodePudding

I need to create a function that'll calculate sales tax given a state and amount to be taxed. If the state doesn't exist in the SalesTax table the function should return 0. But when I run the function it just returns null value even if the state is in the SalesTax table. What it should return is tax rate (per state that is inputted) * quantity (also input)

GO
Create or alter function TaxGivenAState
(@State char(2),@Quantity NUMERIC)
Returns NUMERIC 
as 
BEGIN
    Declare @SalesTaxTotal NUMERIC 
    Declare @TaxRate NUMERIC

    Select @SalesTaxTotal = @TaxRate*@quantity
    From SalesTax s
    Where s.TaxRate = @TaxRate and s.[State]=@State and @TaxRate=s.TaxRate
    If(@SalesTaxTotal=0)
    BEGIN
        Return 0
    END
    ELSE
    BEGIN
        Return @SalesTaxTotal
    END
    return @salestaxtotal
END

CodePudding user response：

You haven't initialised @TaxRate with any value, so it is null when you multiply it with @quantity which will always result in a null:

Declare @TaxRate NUMERIC
Select @SalesTaxTotal = @TaxRate*@quantity

The FROM component has virtuall zero effect here, because you are not using the values from the table at all.

Try this, I've removed the irrelevant aspects of your query

GO
Create or alter function TaxGivenAState
(@State char(2),@Quantity NUMERIC)
Returns NUMERIC 
as 
BEGIN
    Declare @SalesTaxTotal NUMERIC 

    Select TOP 1 @SalesTaxTotal = s.TaxRate*@quantity
    From SalesTax s
    Where s.[State]=@State

    return @SalesTaxTotal
END

If you wanted to specify the tax type as a parameter, then you should have included that either as a parameter, or provided a query to determine which type of tax rate to use, if you provide a snippet of your tax table we might be able to suggest other areas where you have gone wrong here.