Default value returned if no rows selected.... 1

[gbaughma]

Let me start by saying I hate tax law.

I have a table that has cities in MN with their tax rates.
I also have to look up for Wisconsin.

So, here's the scenario:

If the State is MN, and the city is in the table, then it needs to return the tax amount.
If the state is MN, and the city is NOT in the table, it needs to return 6.875

Then it needs to do the same thing for wisconsin.

I'm having a hard time nesting the statements properly....

This is the (non-working) code that I have:

IF ( @State = 'MN') 
	BEGIN
	IF EXISTS (SELECT * FROM tbl_Tax  WHERE lower([City]) like  lower(@CityName) AND lower([State]) like (@State)))
		SELECT [TaxRate] FROM tbl_Tax  WHERE lower([City]) like  lower(@CityName) AND lower([State]) like (@State)  
	ELSE
		SELECT '6.875' AS [TaxRate]
	END

ELSE IF ( @State = 'WI')
	BEGIN
		SELECT '5.6' AS [TaxRate]
	END

I haven't fleshed out the Wisconsin part yet, because I'm evidently having syntax issues.... but the Wisconsin area will have to be essentially the same as the MN area.

Any thoughts?

Thanks in advance!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Bleck. Never mind. Too many )'s on one of the lines.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[Auguy]

I had a situation like this recently. I don't know if my solution is the best way, but it works for me. In your scenario add a "default" entry to the table for each state with a blank city or "(DEFAULT)" (or whatever you want to use to indicate a default value) and the appropriate default rate. In your select use an "OR" condition to always select the default city and order the results so the real city is always the first row. Return the table from the select and always use the rate in the first row. This way you will always get a row returned with either a matching city or the default value in the first row.

Auguy
Northwest Ohio

 

[gbaughma]

Not a bad solution. Have a *.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[Auguy]

This also eliminates all of the "Exists" and makes the select much simpler. It allows you to change the default rate without changing the stored procedure. If I was doing it I would also add an effective date or date range as the rates will always be changing. I have done apps involving tax rates and this is the only way I could make it work and it lets you put new tax rates in ahead of time while maintaining a history of the old rates.

Auguy
Northwest Ohio