BorlandTalk.com

Pepe Taboada · Guest

Hi all:
I'd like to know if this code I'm using is the right one for our MS SQL 2000
database.
I have a table which contains Shipping information for our Work Orders
table. There can be one, dozens or sometimes hundreds of different shipping
address per a particular Job Number, yes hundreds.
When a shipping label is printed data from this table called JobShipments is
dump into a table called ShipData for the UPS and FedExp systems to get data
from.
I have 3 scenarios:
1.- The user needs to reprint shipping labels without changing data. The
data in ShipData remains the same.
2.- The user needs to reprint shipping lables with some changed data. The
Data in ShipData needs to be updated.
3.- The user adds shipping addresses and changes some records and reprints
labels. The Data in ShipData needs to reflect the changed data and new
records.

This is my code and my question is: Is this the way to do it? or by Deleting
records and them adding them I'm putting too much stress on the system .
This is done daily by about 10 users about 50-60 times a day each.

if (Select Count(*) from SHIPINFO.dbo.ShipData where JobN = :JobN ) < 1
//if the order is new then enter it.

BEGIN

INSERT INTO SHIPINFO.dbo.ShipData ( JobNumber, JobN, LineItem, CompanyName,
Attention,

Address1, Address2, Address3, City, State, Zip, Phone )

(Select CAST(JobN as VARCHAR(12)) +'-'+ CAST(JobShipmentN as VARCHAR(12)) as
JobNumber,

JobN, JobShipmentN, ShiptoLine1, Attention, ShiptoLine2, ShiptoLine3,
PackingNotes,

City, State, Zip, TelephoneN

from Laminall.dbo.JobShipments

where JobN = :JobN)

Select * from SHIPINFO.dbo.ShipData

END

ELSE
//The order exists delete records and update with new records.
//This part assumes every reprint of records is for new of changed records.
This is where I need help.
BEGIN

Delete from SHIPINFO.dbo.ShipData

where JobN = :JobN

Select * from SHIPINFO.dbo.ShipData

END

Thanks in advance.

Tony J Hopkinson · Guest

I think you needc to separate out your user transactions to match your
chosen db design

ie. if you can create orders with function X
and shipping requirements (addresses) with function Y the function Z.
Ship Order needs to create a new entry for order x against ship
address Y or change ship address Y on order X to Y1 or to Delete
(cancel) the shipping of order X.
After all you cannot ship order X to places Y and Y1 (or can you ?)