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?
I've discovered some of the calculation syntax. Here is what I've written:
SELECT OrderNo + 1 AS 'NextOrderNo'
WHERE OrderNumberID = (SELECT MAX(OrderNumberID) FROM ORDRNUMBERREC)
INSERT INTO ORDRNUMBERREC
However, SQL is not recognizing 'NextOrderNo' as an integer. It thinks it is a variable character. Is there some kind of 'Convert.ToInt32' like there is in Visual Basic?