create proc [dbo].[aspdnsf_getStateTaxRate]
    @StateID int = null,
    @TaxClassID int = null,
    @CustomerID int = null
  
AS
SET NOCOUNT ON 


IF @StateID = 44
BEGIN
SELECT sr.StateTaxID, sr.StateID, sr.TaxClassID, o.taxrate AS TaxRate, sr.CreatedOn, t.Name TaxClass, s.Name StateName
FROM dbo.StateTaxRate sr with (nolock) 
join dbo.TaxClass t with (nolock) on sr.TaxClassID = t.TaxClassID 
join dbo.State s on s.StateID = sr.StateID 
join dbo.Additional_OH_Tax o on s.Abbreviation = o.LocaleAbbr 
WHERE sr.StateID = @StateID 
and sr.TaxClassID = @TaxClassID 
and o.ID = (SELECT county 
FROM Address a
join Customer c with (nolock) on a.CustomerID = c.customerID 
WHERE a.customerID = @CustomerID 
AND c.ShippingAddressID = a.AddressID)
END ELSE 
SELECT sr.StateTaxID, sr.StateID, sr.TaxClassID, sr.TaxRate, sr.CreatedOn, t.Name TaxClass, s.Name StateName
FROM dbo.StateTaxRate sr with (nolock) 
join dbo.TaxClass t with (nolock) on sr.TaxClassID = t.TaxClassID 
join dbo.State s on s.StateID = sr.StateID
WHERE sr.StateID = COALESCE(@StateID, sr.StateID) 
and sr.TaxClassID = COALESCE(@TaxClassID, sr.TaxClassID)


GO


create proc [dbo].[aspdnsf_getStateTaxRateByID]
    @StateTaxID int,
    @CustomerID int
  
AS
SET NOCOUNT ON 

IF @StateTaxID = 2
BEGIN
SELECT sr.StateTaxID, sr.StateID, sr.TaxClassID, o.TaxRate, sr.CreatedOn, t.Name TaxClass, s.Name StateName
FROM dbo.StateTaxRate sr with (nolock) 
join dbo.TaxClass t with (nolock) on sr.TaxClassID = t.TaxClassID 
join dbo.State s on s.StateID = sr.StateID
join dbo.Additional_OH_Tax o on s.Abbreviation = o.LocaleAbbr 
WHERE StateTaxID = @StateTaxID
AND o.ID = (SELECT county 
FROM Address a
join Customer c with (nolock) on a.CustomerID = c.customerID 
WHERE a.customerID = @CustomerID 
AND c.ShippingAddressID = a.AddressID)
END ELSE
SELECT sr.StateTaxID, sr.StateID, sr.TaxClassID, sr.TaxRate, sr.CreatedOn, t.Name TaxClass, s.Name StateName
FROM dbo.StateTaxRate sr with (nolock) join dbo.TaxClass t with (nolock) on sr.TaxClassID = t.TaxClassID join dbo.State s on s.StateID = sr.StateID
WHERE StateTaxID = @StateTaxID


GO