Sql-server – SQL Server 2014 complex Stored Procedure

sql serversql server 2014stored-procedures

Sorry for the Title but I haven't found a better explanation.

I need to generate a TrackingID number for shipments using data from 3 different tables:

  • Sales
  • ShippingAddress
  • Carriers

I built a query that increments the appropriate trackingNumber and returns it together with some extra columns. (In the real query data are stored in a temp table and other queries follow, but now the issue is here.)

Here it is

DECLARE @OrderID NVARCHAR(10) = 'B3'

UPDATE cc  
SET NextSeed= IIF(nextSeed IS NULL, NewStartSeed + 1,
                  IIF(nextSeed = LastSeed, NewStartSeed, NextSeed + 1)),
    LastSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NewStopSeed, LastSeed),
    NewStartSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NULL, NewStartSeed),
    NewStopSeed = IIF(NextSeed=LastSeed OR NextSeed IS NULL, NULL, NewStopSeed)
OUTPUT
    COALESCE(deleted.NextSeed, inserted.nextSeed-1) CurrentSeed, 
    inserted.Prefix, inserted.Suffix,
    inserted.ShippingService, inserted.CarrierCode
FROM
    Sales s
JOIN
    ShippingAddress ss ON s.shippingAddressID = ss.ShippingAddressID
JOIN
    Carriers cc ON cc.ShippingService = s.ShippingService 
                AND cc.carriercode = s.carriercode
LEFT JOIN
    SurchargeAreas ssa ON ss.PostalCode = ssa.PostalCode
WHERE
    s.OrderID = @OrderID

Now I have to add an extra table that contains, for the different carriers, the PostalCodes with Surcharge:

SurchargeAreas Table

What I need is:

  • if Customer's PostalCode is in SurchargeArea for the selected Carrier I need to change carrier/shippingService to default Carrier/shippingService to avoid extra shipping costs.

I added to the above query the following extra condition:

and ssa.postalcode is null

That detects if the customer's postalcode is in a surcharge area, but returns zero result, and therefore does not select the default shipping service, does not increment the Seed and does not return a result.

Here is the sqlfiddle with all tables and some sample data to better understand.

In the example, the PostalCode of OrderID "B4" is in a surcharge area, while B1, B2 and B3 are not.

Therefore setting 

@OrderID = 'B1' or 'B2' or 'B3'

returns data, while

@OrderID = 'B4'

does not return anything, while I would expect something like

31001     RS    NL   postnlrm    postnl

since postnlrm has 'default' column set to 1

Can you suggest how to achieve what I'm looking for?

Thanks.

Best Answer

Because you're essentially asking for the condition of joins to be changed through a lookup ("if my postalcode is in a surcharge area, change my carrier code joins to this, otherwise this"), I would recommend that you split this update into two sets. Here's some pseudocode to help get you started:

DECLARE @OrderID nvarchar(10)='B4'
DECLARE @mytable TABLE (CurrentSeed, Prefix, Suffix, ShippingService, CarrierCode)

-- Update Surchargeable Areas with Defaults; this assumes only ONE default for a postal code
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN SurchargeAreas     ssa on  ss.PostalCode=ssa.PostalCode
JOIN dbo.Carriers           cc  on cc.carriercode=ssa.carriercode
      and cc.[Default] = 1  -- terrible practice using a SQL keyword as a column name, by the way!!
WHERE s.OrderID=@OrderID;

-- Update non-Surchargeable Areas
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN Carriers cc ON cc.ShippingService = s.ShippingService 
       AND cc.carriercode = s.carriercode
LEFT JOIN SurchargeAreas        ssa on  ss.PostalCode=ssa.PostalCode
AND cc.[Default] = 0
WHERE s.OrderID=@OrderID
          AND ssa.ID IS NULL;
Related Question