Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.