sql stored proceedures

[Papa_Don]

Hi group,

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!

Don

[Papa_Don]

I've discovered some of the calculation syntax. Here is what I've written:

USE DataDesignSolutions
SELECT OrderNo + 1 AS 'NextOrderNo'
FROM ORDRNUMBERREC
WHERE OrderNumberID = (SELECT MAX(OrderNumberID)  FROM ORDRNUMBERREC)
INSERT INTO ORDRNUMBERREC
(OrderNo, CreateDate)
VALUES('NextOrderNo', '05/06/2013')

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?

Again, thanks for your help.

Don

[Papa_Don]

To create the stored procedure, I've gotten this far:

CREATE PROCEDURE Update_OrderNo @ordNo INT output, @ordno1 INT output, @today DATETIME output
AS
SELECT
@ordNo = 'OrderNo',
@ordno1 = @ordNo + 1,
@today = GETDATE()
SELECT @ordNo
FROM [DataDesignSolutions].dbo.ORDRNUMBERREC
WHERE OrderNumberID = (SELECT MAX(OrderNumberID)
INSERT INTO [DataDesignSolutions].dbo.ORDRNUMBERREC
(OrderNo, CreateDate)
VALUES(@ordno1, @today)

Unfortunately, it doesn't like "INSERT INTO".

Does anyone understand this enough to help me finish it?

Don

[Papa_Don]

Found the whole answer:

CREATE PROCEDURE Update_OrderNo @ordno INT output, @ordno1 INT output, @today DATETIME output
AS
SELECT @today = GETDATE(), @ordno = OrderNo FROM ORDRNUMBERREC
SELECT MAX(OrderNumberID), @ordno FROM ORDRNUMBERREC
SELECT @ordno1 = @ordno + 1
INSERT INTO ORDRNUMBERREC
(OrderNo, UserID, CreateDate)
VALUES(@ordno1, 'system', @today)

This did exactly what I needed it to do.

Don