I need to setup sales tax at the rate of 8.75% for a single municipality, 4% for the rest of the state and no tax for other states.

Would the following be the correct parameters for the stored procedure?

ALTER PROCEDURE [dbo].[Smith_GetTaxRate]

@CityName VARCHAR(255) ,

@State VARCHAR(50)

AS

IF ( @State = 'LA')

BEGIN

SELECT '4' AS [TaxRate]

END

ELSE

IF ( @CityName = 'SLIDELL' )

BEGIN

SELECT '8.75' AS [TaxRate]

END

ELSE

BEGIN
SELECT [TaxRate] FROM Smith_Tax

WHERE lower([City]) like lower(@CityName)

AND lower([State]) like (@State)

END

I've attempted various ways of configuring the stored procedure for dbo.Smith_GetTaxRate but the result has been constant.

No tax charged for other states, 4% tax charge for the state of Louisiana, which are as expected. But for Slidell the tax rate is 8.75% but 4% is the rate that is charged.

Below is the configuration of the Tax Setup

Tax Setup

Charge Tax All States
Charge Tax in Home State
Use Tax Tables - selected
Charge No Tax
Tax Rate:
Enable VAT/GST Tax: not checked

This is my stored procedure. When I execute the storaged procedure I don't have any errors reported. Any clues as to why it's not applying the correct tax rate?

USE [f_davis]
GO
/****** Object: StoredProcedure [dbo].[Smith_GetTaxRate] Script Date: 02/09/2010 10:41:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Smith_GetTaxRate]

@CityName VARCHAR(255) ,

@State VARCHAR(50)

AS

IF ( @State = 'LA')

BEGIN

SELECT '4' AS [TaxRate]

END

ELSE

IF ( @CityName = 'Slidell')

BEGIN

SELECT '8.75' AS [TaxRate]

END

ELSE

BEGIN

SELECT [TaxRate] FROM Smith_Tax

WHERE lower([City]) like lower(@CityName)

AND lower([State]) like (@State)

END

Tax Setup

I've tried this:

ALTER PROCEDURE [dbo].[Smith_GetTaxRate]

@CityName VARCHAR(255) ,

@State VARCHAR(50)

AS

IF ( @State = 'LA')

BEGIN

SELECT '4' AS [TaxRate]

END

ELSE

IF ( @CityName = 'Slidell' )

BEGIN

SELECT '8.75' AS [TaxRate]

END

ELSE

BEGIN

SELECT [TaxRate] FROM Smith_Tax

WHERE lower([City]) like lower(@CityName)

AND lower([State]) like (@State)

END

Then I tried this:

ALTER PROCEDURE [dbo].[Smith_GetTaxRate]

@CityName VARCHAR(255) ,

@State VARCHAR(50)

AS

BEGIN

IF ( @State = 'LA')

SELECT '4' AS [TaxRate]

ELSE

IF ( @CityName = 'Slidell')

SELECT '8.75' AS [TaxRate]

ELSE

SELECT [TaxRate] FROM Smith_Tax

WHERE lower([City]) like lower(@CityName)

AND lower([State]) like (@State)

END

This is my current Stored Procedure

USE [f_davis]
GO
/****** Object: StoredProcedure [dbo].[Smith_GetTaxRate] Script Date: 02/09/2010 18:32:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Smith_GetTaxRate]

@CityName VARCHAR(255) ,

@State VARCHAR(50)

AS

BEGIN

IF ( @State = 'LA')

SELECT '4' AS [TaxRate]

ELSE

IF ( @CityName = 'Slidell')

SELECT '8.75' AS [TaxRate]

ELSE

SELECT [TaxRate] FROM Smith_Tax

WHERE lower([City]) like lower(@CityName)

AND lower([State]) like (@State)

END

I think my problem is here:

I tried setting the tax rates as the Values and also the City/State as the Values.

Thank you, that did the trick. Now I've found that I'm having a problem with the Category search function.

A critical error has occurred.
Conversion failed when converting the varchar value 'Cookware' to data type int.