I have a stored procedure in which the user selects to view sales by either Units / Employees/ State (in the real application there are 10 options)
This inserts into a #temp table via the following logic:
if @ReportOption = 'Units'
begin
insert into #Sales
(
OrderID,
CustomerID,
OptionID,
OptionName
)
select
OrderID,
CustomerID,
isnull((select UnitID from Orders o where o.OrderID = t.OrderID),0),
isnull((select UnitName from Units u where o.UnitID = u.UnitID),0),
from #temp t
end
if @ReportOption = 'States'
begin
insert into #Sales
(
OrderID,
CustomerID,
OptionID,
OptionName
)
select
OrderID,
CustomerID,
isnull((select StateID from Orders o where o.OrderID = t.OrderID),0),
isnull((select StateName from [States] u where o.StateID = u.StateID),0),
from #temp t
end
Is there a better way to do this please? In other words, replace the 10 "if" statement with something more efficient.
It is just the 3rd and 4th column that could change based on one of 10 report options that the user can select.