Hi,
I have 3 Textboxes in my Web page. When user key in value in these 3 textboxes,
their result should show on a "Label"
1) Gross Amount
2) Taxable Amount
3) Tax Amount
I have to write a Stored Procedure for calculating the value of above textboxes.
Condition is:
If Gross Amount is > or < 0.00, then Taxable and Tax amount should calculate based on below formula:
@TaxableAmt = @GrossAmt / @Rate
@TaxAmt = @GrossAmt - @TaxableAmt
but If Taxable Aount is > or < 0.00, then Gross and Tax amount should calculate based on below forumla:
@GrossAmt = @TaxableAmt * @Rate
@TaxAmt = @GrossAmt - @TaxableAmt
and for "Tax Amount" I have 2 different conditions:
If Tax Amount is = 0.00, then Taxable and Gross amount should calculate based on below formula:
@TaxableAmt = @TaxAmt / (@Rate-1)
@GrossAmt = @TaxAmt + @TaxableAmt
but If Tax Amout is <> 0.00, then whatever the value of Tax Amount is, it should key-in/show as it is.
i.e., @TaxAmt = @TaxAmt
For this requirement, I wrote below SP:
ALTER PROCEDURE [Invoice].[usp_tbl_TaxCode_Update]
-- Add the parameters for the stored procedure here
@TaxCodeID nvarchar(50) ,
@GrossAmt decimal(18, 2),
@TaxableAmt decimal(18, 2),
@TaxAmt decimal(18, 2)
AS
BEGIN
If(@GrossAmt > 0.00 OR @GrossAmt < 0.00)
begin
set @TaxableAmt = @GrossAmt / @Rate
Set @TaxAmt = @GrossAmt - @TaxableAmt
end
IF(@TaxableAmt > 0.00 OR @TaxableAmt < 0.00)
begin
set @GrossAmt = @TaxableAmt * @Rate
Set @TaxAmt = @GrossAmt - @TaxableAmt
end
IF(@TaxAmt = 0.00)
begin
set @TaxableAmt = @TaxAmt / (@Rate-1)
set @GrossAmt = @TaxAmt + @TaxableAmt
end
IF (@TaxAmt > 0.00)
begin
set @TaxAmt = @TaxAmt
end
Update [Invoice].[tbl_TaxCode]
Set
LIGrossAmt = IsNull(@GrossAmt,LIGrossAmt),
LITaxableAmt = IsNull(@TaxableAmt,LITaxableAmt),
LITaxAmt = IsNull(@TaxAmt,LITaxAmt),
Where LITaxCodeID = @TaxCodeID
END
But its not working as per required, please reply what is wrong in SP code ??
