I am writing a stored procedure that will insert an order and then assign N serial numbers to that order id and I am wondering if you kind folk can give me any insight into which approach would be better practice (or if I am missing a third option?).
The options that I have come up with are below
Or
Thanks in advance!!
Willie
The options that I have come up with are below
Code:
CREATE PROCEDURE dbo.InsertOrder (
@Quantity int,
@MerchantId int,
@ShippingFirstName varchar(30),
@ShippingLastName varchar(30),
@ShippingAddress1 varchar(50),
@ShippingAddress2 varchar(50),
@ShippingCity varchar(50),
@ShippingState varchar(20),
@ShippingZip varchar(12),
@ProductId varchar(20)
)
AS
BEGIN
SET NOCOUNT ON;
Declare @InsertedOrderId int,
@i int = 0;
INSERT INTO dbo.OrderTable(ShippingFirstName,ShippingLastName,ShippingAddress1,ShippingAddress2,ShippingCity,ShippingState,ShippingZip,MerchantId,Quantity,OrderDate)
Values(@ShippingFirstName,@ShippingLastName,@ShippingAddress1,@ShippingAddress2,@ShippingCity,@ShippingState,@ShippingZip,@MerchantId,@Quantity,SYSDATETIME())
SELECT @InsertedOrderId = SCOPE_IDENTITY();
While @i <= @Quantity
BEGIN
update dbo.KeyMaster
set MerchantId = @MerchantId, PurchaseDate = SysDateTime(), OrderId = @InsertedOrderId
WHERE AccessKey in (select top 1 AccessKey
from dbo.KeyMaster
where MerchantId IS NULL
and ProductId = @ProductId)
SET @i = @i+1
END
END
Or
Code:
CREATE PROCEDURE dbo.InsertOrder (
@Quantity int,
@MerchantId int,
@ShippingFirstName varchar(30),
@ShippingLastName varchar(30),
@ShippingAddress1 varchar(50),
@ShippingAddress2 varchar(50),
@ShippingCity varchar(50),
@ShippingState varchar(20),
@ShippingZip varchar(12),
@ProductId varchar(20)
)
AS
BEGIN
SET NOCOUNT ON;
Declare @InsertedOrderId int,
@i int = 0;
INSERT INTO dbo.OrderTable(ShippingFirstName,ShippingLastName,ShippingAddress1,ShippingAddress2,ShippingCity,ShippingState,ShippingZip,MerchantId,Quantity,OrderDate)
Values(@ShippingFirstName,@ShippingLastName,@ShippingAddress1,@ShippingAddress2,@ShippingCity,@ShippingState,@ShippingZip,@MerchantId,@Quantity,SYSDATETIME())
SELECT @InsertedOrderId = SCOPE_IDENTITY();
While @i <= @Quantity
BEGIN
; with cte as
(select top @Quantity AccessKey
from dbo.KeyMaster
where MerchantId IS NULL
and ProductId = @ProductId
)
update cte
set MerchantId = @MerchantId, PurchaseDate = SysDateTime(), OrderId = @InsertedOrderId
SET @i = @i+1
END
END
Thanks in advance!!
Willie