Rewrite the query to convert multiple select statements into one select-CodePudding

I have the following procedure. It has 4 select statements and one of them is selected based on the parameters passed in. Every time I have to add a new column, I have to make sure I add it to all the select statements. If I miss adding a column to one of the select statements then the application encounters an error. I'm trying to find a way to just have one select statement and get the appropriate results based on the parameters passed in. I don't want to use dynamic SQL to achieve it. Is there any other ways to do it? Thanks!

Create Procedure usp_region_order
@regionId varchar(100),
@custId   varchar(100)
As
Begin

If @regionId = 'North'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionNorth rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
Else If @regionId = 'South'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionSouth rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
Else If @regionId = 'East'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionEast rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
Else If @regionId = 'West'
Begin
    Select o.orderId, o.ordername, c.custName, c.custPhone
    From dbo.customer c
      Join dbo.order o
      on c.custId = o.custId
      Join dbo.region r
      on o.regionId = r.regionId
      Join dbo.regionWest rn
      on r.regionId = rn.regionId
    Where c.custId = @custId
      And r.regionId = @regionId
End
End /* procedure ends */

CodePudding user response：

You could form an inline table of all regions, and then use a single one shot query:

BEGIN
    SELECT o.orderId, o.ordername, c.custName, c.custPhone
    FROM dbo.customer c
    INNER JOIN dbo.order o ON c.custId = o.custId
    INNER JOIN dbo.region r ON o.regionId = r.regionId
    INNER JOIN
    (
        SELECT regionId, 'North' AS region FROM dbo.regionNorth UNION ALL
        SELECT regionId, 'South' FROM dbo.regionSouth           UNION ALL
        SELECT regionId, 'East' FROM dbo.regionEast             UNION ALL
        SELECT regionId, 'West' FROM dbo.regionWest
    ) rn
        ON rn.regionId = r.regionId
    WHERE
        c.custId = @custId AND
        r.regionId = @regionId AND
        rn.region = @regionId
END