SP to Insert Multiple Rows w/ Manual AutoNumber

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP to Insert Multiple Rows w/ Manual AutoNumber

Author

Topic

grrr223
Starting Member

30 Posts

Posted - 2004-07-06 : 11:41:22

Due to business requirements outside of my control, I need to assign order numbers to orders in distinct ranges based on which supplier's product they are for. Thanks to the help of the good people here, I have been able to do this...for one order at a time. I would appreciate any help on the best way to insert multiple orders (say, 20 at a time max) that would guarantee that they are consecutive numbers, which I assume would mean rolling it all up inside a transaction. Anyway...

I have a table called dbo.zstlkpOrd_NoRange that has the following fields:

Ord_NoRangeName varchar(50)
Ord_NoRangeMin int
Ord_NoRangeMax int

The table's data looks like this, (ranges aren't necessarily consecutive in actual database)

ProductA,        1,  500000
ProductB,  1000000, 2999999
ProcuctC,  3500000, 3999999

And the stored procedure that I use to insert records into my Orders table looks like this:

ALTER PROCEDURE dbo.spInsertReleaseNumbers
	
	(@chvOrd_NoRangeName varchar(50),
	@chvItem_No varchar(15),
	@intQty int)

AS

BEGIN

	DECLARE @intOrd_NoRangeMin as int
	DECLARE @intOrd_NoRangeMax as int
	DECLARE @intOrd_NoNew as int
	
	--Lookup Order No. Range Minimum
	Set @intOrd_NoRangeMin = (SELECT Ord_NoMin
					FROM dbo.zstlkpOrd_NoRange
					WHERE company = 'data_05' AND item_desc = @chvRange)
	
	--Lookup Order No. Range Maximum
	Set @intOrd_NoRangeMax = (SELECT Ord_NoMax
					FROM dbo.zstlkpOrd_NoRange
					WHERE company = 'data_05' AND item_desc = @chvRange)
	
	--Get the next order number in that range
	Set @intOrd_NoNew = (SELECT MAX(ord_no)
				FROM dbo.MC_ReleaseNumbers_fn(@chvCompany)
				WHERE ord_no between @intOrd_NoRangeMin AND @intOrd_NoRangeMax) + 1
	
	--Add New Order to Orders table
	INSERT INTO dbo.Orders (ord_no, item_no, qty)
	VALUES (@intOrd_NoNew, @chvItem_No, @intQty)

End

How can I pass this procedure @intNumberOfOrders and have it insert multiple rows with consecutive order numbers?
I have more experience with VB, so my firt thought would be something along the lines of:

 For Each OrderNo from 1 to @intNumberOfOrders
...

I'm all for learning new things, so I don't need the code written for me or anything, just some direction.

Thank you!!!

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-12 : 08:40:30

Use a WHILE loop - something like...


ALTER PROCEDURE dbo.spInsertReleaseNumbers
	
	(@chvOrd_NoRangeName varchar(50),
	@chvItem_No varchar(15),
	@intQty int,
        @intNoOfOrders int)

AS

BEGIN

	DECLARE @intOrd_NoRangeMin as int
	DECLARE @intOrd_NoRangeMax as int
	DECLARE @intOrd_NoNew as int
        DECLARE @intOrdCount INT
	
SET @intOrdCount=1
        --lookup min/max
        SELECT @intOrd_noRangeMin=Ord_NoMin,
               @intOrd_noRangeMax=Ord_NoMax
        FROM dbo.zstlkpOrd_NoRange
        WHERE company='data_05' AND item_desc=@chvRange

WHILE @intOrdCount <= @intNoOfOrders BEGIN	
	--Get the next order number in that range
	SELECT @intOrd_NoNew=MAX(ord_no)+1
	       FROM dbo.MC_ReleaseNumbers_fn(@chvCompany)
	       WHERE ord_no between @intOrd_NoRangeMin 
               AND @intOrd_NoRangeMax
	
	--Add New Order to Orders table
	INSERT INTO dbo.Orders (ord_no, item_no, qty)
	VALUES (@intOrd_NoNew, @chvItem_No, @intQty)

       SELECT @intOrdCount=@intOrdCount+1
END

End

Also note the slight change in syntax of your SELECT statements that fetch the numbers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources