How to Write a Process Order Stored Procedure in T-SQL
PERSISTANT STORED MODULE CHOSEN: PROCESS ORDER
This Persistent stored module Process Processes Orders by reading the oldest unfulfilled Order and Flags it status as Fulfilled when there is enough stock to fulfil other or leaves to Unfulfilled or Awaiting Stock otherwise.
Database Structure
5.1 Pseudocode
START
Get oldest Unfufilled Order data
Get the catalogue data for that Order
For Each Catalogue Item
If Catalogue Item type is Stock
Then
if Catlogue Item available stock> Catalogue Order Quantity
Then
Set Order Status =Fufilled
Else
Set Order Status=Awaiting Stock
STOP
Else if Catalogue Item type is Non-Stock
Create supplier Order
If Error in then return transaction to former state
STOP
5.2 Actual code
CREATE PROCEDURE [dbo].[ProcessOrder]
— Add the parameters for the stored procedure here
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
declare @catId varchar(max)
declare @count int –count
declare @iRow int –showing number of rows
declare @itemtype varchar(50)
declare @available_stock int
declare @orderqty int
declare @Orderid varchar(max)
declare @checkAwaiting int
— Insert statements for procedure here
DECLARE @tbl TABLE(
RowID INT IDENTITY(1, 1),
CatlID VARCHAR(Max),
Qty INT,
OrderID VARCHAR(MAX))
INSERT @tbl
SELECT Catl_id,Quantity,Order_id from dbo.VwUnfufilledOrders where VwUnfufilledOrders.Order_date=(Select min(VwUnfufilledOrders.Order_date)
from dbo.VwUnfufilledOrders)
SET @count = @@ROWCOUNT
SET @iRow = 1
set @checkAwaiting=0
Select @count
Begin transaction— start transcation
–call function for availability
—start loop to check for availabilty of every catlogue item
WHILE @iRow <= @count
begin
Select @catId=CatlID,@orderqty=Qty,@Orderid=OrderID from @tbl where RowID=@iRow
select @itemtype=Item_Type, @available_stock=Available_stock from dbo.Catlogue_Item where Catl_id=@catId
if (@itemtype=‘Stock’)
begin
print ‘Order id is’+@Orderid+‘with number of rows=’+ convert(varchar,@count) +‘it is a stock item’
if(@available_stock>@orderqty)
begin
print ‘Oder id is’+@Orderid+‘ /with Qty=’+ convert(varchar(50),@orderqty) +‘and available stock=’+convert(varchar(50),@available_stock)
Update dbo.CustOrder set Status=‘Fufilled’ where Order_id=@Orderid
If @@error <> 0
goto ERR_HANDLER
end
else
begin
Update dbo.CustOrder set Status=‘UnFufilled’ where Order_id=@Orderid
print ‘Qty not enough’
set @checkAwaiting=1
goto ERR_HANDLER
— stock insufficient so Order status remains unfufilled
–call stock replenish function
end
end
if (@itemtype=‘NonStock’)
begin
— create supplier order
print ‘Its a supplier direct order’
end
SET @iRow = @iRow + 1
end
Commit Transaction
Return 0
ERR_HANDLER:
Select ‘Unexpected error occurred!’
Rollback transaction
if ( @checkAwaiting=1)Update dbo.CustOrder set Status=‘AwaitingStock’ where Order_id=@Orderid— to be removed
Return 1
END