SQL Server 2014 complex Stored Procedure

Question

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.

• codedawg82: You can't return that record when the CarrierCode for that Sales order is associated with dhl and not postnl. Are you asking to (at times) ignore the CarrierCode relationship between the Carriers and Sales tables?
• Joe: Yes, if the postalcode is in SurchargeArea, it has to override the selected carrier/service and use the default one.
• codedawg82: Are you constrained to one UPDATE? It sounds like you coudld easily do this in two UPDATE statements: one for sales records that are in SurchargeAreas and one for the remainder. You could store your OUTPUT's results by using the frame OUTPUT COALESCE(deleted.NextSeed, inserted.nextSeed-1) CurrentSeed, inserted.Prefix, inserted.Suffix, inserted.ShippingService, inserted.CarrierCode INTO @Table...
• Joe: Sorry, not well understood your question: That above is only part of the query and actually the output goes to a temporary table and then other queries follows. You mean to execute this query and if result is null to make another query with default parameters? I already thought about this, and once understood how to check if result is null, probably will work, but was looking for a better solution, if exists (but always exists!)

Answer 1

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;

• Joe: This procedure is for each single shipment: I think is better to first check if the given postal code is in surcharge area and then according to the result use the 'standard' parameters or override the shipping service: something like if not exists (select 1 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 and ssa.postalcode is null) THEN BEGIN....END ELSE BEGIN... END
• Joe: but I was looking for a more elegant solution
• Joe: this is my 'unelegant' solution: sqlfiddle
• codedawg82: I've seen more inelegant solutions :) You're certainly welcome to do the check first and and apply control logic to decide which UPDATE you want to pursue. I tend to try to use relational sets as best I can instead of specifying control logic, but there could be many factors in one's decision.
• Joe: Yes, that's what I was looking for. Mine is procedural logic, that I do not like when working with SQL, but haven't find a better solution, and I've not understood how to apply your. Anyway, thanks for your time!

Answer 2

Cant you COALESCE and return default courier for the area, if courier is NULL or if a surcharge area from the surchargarea table before u join it?

I.e Left join (select top 1 coaleacse blah from surchargeare) x On x.postalcode=ss.postalcode.

Sorry if it is not clear, i am not at a desk