Ethelcofie’s How To Articles

July 29, 2008

How to Write a Process Order Stored Procedure in T-SQL

Filed under: Development Articles — ethelcofie @ 2:02 pm
Tags: , ,

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

Stock control database

Stock control database

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: