**Hi, I'm after someone to write two sql server stored procedures:
The first one simply adds a new record to a table. If the record already exists it will update the existing one (set bit field to true).
fldInquiryRecordID - primary key
fldLockTimeMaterialLabour - bit
Basically I want to pass in the inquiry record id from a passthrough query and set fldLockTimeMaterialLabour to true
if the record doesn't exist it will need to add it.
The second procedure is more complex.
It will need to add a new records to a couple of tables based on records from other tables.
There are five tables tblDraftInvoice, tblDraftInvoiceSub, tblInvoice, tblInvoiceSub and tblInquiry
I need to pass in an id value from tblDraftInvoice and a four letter string for fldFranchiseCode. It will copy the record from tblDraftInvoice to tblInvoice (based on the id passed in) it will also need to copy all
associated sub records from tblDraftInvoiceSub to tblInvoiceSub.
Once copied it will need to get the new primarykey value from tblInvoice and update fldInvoiceRecordID in the original record in tblDraftInvoice.
The procedure will also need to return the new fldInvoiceRecordID when its called.
Other things to note:
There is a field in tblInvoice called fldInvoiceID. This is a numeric field, the procedure will need to populate a new value in here which is an increment on the highest value from tblInvoice for the franchisecode. To work out the franchise you will need to link tblInvoice to tblInquiry via fldInquiryRecordID in tblInquiry there is a field fldFranchiseCode.
The franchisecode will need to be passed in when it is called it is just a four letter text value. Then get the highest fldInvoiceID from tblInvoice for the specified franchise and increment it by one. I guess you need to be careful about someone else adding a new invoice whilst the current one is run.
I'm not after someone who thinks they can do it in 5 minutes, I would prefer to pay someone to spend a few hours on it (it could easily be done in a day) and ask questions so that it works correctly.