SQL - Change Tax Type

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Alexander Toufexis
-- Create date: 27/05/2015
-- Description:	This Stored Proc enables the easy changing of a tax type to another tax type. Do note that the tax types should already be the same percentage, otherwise there will be issues.
-- =============================================
CREATE PROCEDURE _as_ChangeTaxType 
	-- Add the parameters for the stored procedure here
	@AuditNumber varchar(50), 
	@OldTaxType varchar(10),
	@NewTaxType varchar(10) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


--USAGE:
--EXEC _as_ChangeTaxType '28929.0020', '6', '7'

	--DECLARES:
	DECLARE @OldTaxID as int
	DECLARE @NewTaxID as int
	DECLARE @OldTaxPerc as float
	DECLARE @NewTaxPerc as float
	DECLARE @InvNumID as bigint

	--Check if tax types have the same percentage.
	SELECT @OldTaxID = idTaxRate, @OldTaxPerc = TaxRate FROM TaxRate WHERE Code = @OldTaxType
	SELECT @NewTaxID = idTaxRate, @NewTaxPerc = TaxRate FROM TaxRate WHERE Code = @NewTaxType

	--CHECK if percentages are the same:
	IF (@OldTaxPerc <> @NewTaxPerc) 
		BEGIN
		RaisError('Tax Rates are not the same!',17,1)
		RETURN -1
		END

	--Getting InvNumID to update invoice lines:
	SELECT @InvNumID = COALESCE((SELECT InvNumKey FROM PostAP WHERE cAuditNumber = @AuditNumber),(SELECT InvNumKey FROM PostAR WHERE cAuditNumber = @AuditNumber),0)
	--UPDATE:
	UPDATE PostGL SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
	UPDATE PostAR SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
	UPDATE PostAP SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
	UPDATE PostST SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
	UPDATE _btblInvoiceLines SET iTaxTypeID = @NewTaxID WHERE iInvoiceID = @InvNumID AND iTaxTypeID = @OldTaxID

	PRINT 'Updated Tax Types for Audit Number: ' + @AuditNumber
END
GO