How to edit stored procedure, ELSE IF statement...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to edit stored procedure, ELSE IF statement...

Author

Topic

claireyfairy
Starting Member

1 Post

Posted - 2010-05-13 : 11:20:05

which looks up VAT rate.

We have a bespoke system which is no longer supported by the sole programmer that wrote it.

I have the admin system and the VAT rate is looked up on creation of an invoice or entering a Purchase Invoice.

It is a stored procedure which currently reads:

Create Proc spGNvatRate
@Date datetime,
@Code varchar(1),
@Return real = 0 output

As

Set Nocount On
Set @Return = 0

-- Standard Rate
IF @Code = 'S'
BEGIN
IF @Date < '12/01/2008' or @Date > '12/31/2009'
Set @Return = 17.5
ELSE
Set @Return = 15
END

I want to know how to add in another IF say if there was a new VAT rate but still only one rate at a time.

BEGIN
IF this
Set this
ELSE IF this
Set this
Else
Set this
END

Say from 31/12/2010 the VAT rate is 20% then what would I change the code to??

Many Thanks,
Claire

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 11:32:05

the best way to do this is to remove the hardcodings from procedure logic and put the VAT rate in a table with valid_from and valid_to dates. this way you dont have edit procedure often whenever VAT changes. in process just retrieve it like


SELECT @Return =VAT_Rate
FROM VAT_Table
WHERE @Date BETWEEN Valid_From AND Valid_To

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources