If you've seen my posts before on SQL Server stuff you know I'm a "raw rookie" with this. Again, I need some help.
I'm going to need SQL to create an order number for me. I've created a datatable with two fields: OrderNoID (key) and OrderNumber. I've already added the lowest order number I would use as the first line of the table (100000). I new need to do these steps in this order:
1) "lock" the data table. (I want to prevent anyone from accessing this table until the routine is completed)
2) Find the highest (or top) OrderNoID.
3) From this top ID number, get the OrderNumber
4) Add OrderNumber plus 1 creating a new OrderNumber
5) Create the next OrderNoID (and row) and insert this new OrderNumber
6) Return the new OrderNoID and new OrderNumber values to be read by a variable in Visual Basic.
7) Unlock the data table and close the proceedure (if that's required)
I've read through several articles but I'm unclear how to write such a proceedure as well as to name it and store it. Can someone help me through this?
In advance, thanks for your help!