December 16, 2006 at 1:05 am
Hello everyone i want to make a SP in the Sql Server 2000 , for this i need the Follwing tables ( i know post is length but i m in very trouble and ant handle this so thats y i try to define my problem in best way)
1) Batches::::
BatchID int IDENTITY,
SupplierID int NOT NULL,
BatchMediaTypeID int NOT NULL,
TotalPinsIssued int NULL,
TotalPinsProcessed int NULL,
BatchIssueDate datetime NOT NULL,
BatchProcessDate datetime NULL,
TotalFaceValue numeric(10,3) NULL,
DiscountedPrice numeric(10,3) NULL,
Commission numeric(5,2) NULL,
ApprovalStatusCode int NOT NULL,
SupplierBatchNumber varchar(50) NULL
2)Pins::::
PinID int IDENTITY,
BatchID int NOT NULL,
PinPackageCode int NOT NULL,
PinStatusCode int NOT NULL,
CityID int NULL,
PinCode varchar(30) NOT NULL,
PinPrice numeric(10,2) NOT NULL,
PinSerial varchar(30) NULL,
PinIssueDate datetime NULL,
PinExpireDate datetime NULL
3)Transactions::::::::::
TransactionID int IDENTITY,
VoucherID int NOT NULL,
BuyerID int NOT NULL,
TransactionTypeID int NOT NULL,
TransactionStatusCode int NOT NULL,
SystemUserID int NULL,
ApproverID int NULL,
IsDebit smallint NOT NULL,
TransactionDate datetime NOT NULL,
DispatchDate datetime NULL,
ExpireDate datetime NULL,
LastStatusUpdate datetime NULL,
TotalAmount numeric(10,2) NOT NULL,
TotalDiscount numeric(10,2) NOT NULL,
TotalParentDicount numeric(10,2) NULL,
Tax numeric(10,2) NOT NULL,
PaymentServiceFees numeric(10,2) NOT NULL,
InstrumentNumber varchar(50) NULL,
Comments varchar(500) NULL
4) TransactionsLineItems
TransactionLineItemID int IDENTITY,
TransactionID int NOT NULL,
PinID int NOT NULL,
Price numeric(10,2) NOT NULL,
Discount numeric(10,2) NOT NULL,
ParentDiscount numeric(10,2) NULL
5)PendingTransactions::::::::::
PendingTransactionID int IDENTITY,
BuyerID int NOT NULL,
SubAccountNumber smallint NULL,
SessionID varchar(64) NOT NULL,
PinPackageCode int NOT NULL,
Quantity int NOT NULL
6)PendingTransactionLineItems::::::::::
PendingTransactionID int NOT NULL,
PinID int NOT NULL
I have a webpage in this page user have PinPackages and then he selects the qunantity of the pins of that packages for this i used the table PendingTransactions ( see Quantity n PinPackagesCodes and user Info i.e BuyerID)now the Pins he wants to purchase goes to PendingTransactionLineItems ( in this PinID present which he wants to buy).
//////WORK TO DO ///////
Now i want to do,get the qunatity from that table (Pending Transactions)and go to Batches Table it selects the Pins (according to Quantity e.g 5 Pins, from Previous BatchDate if available pins of Previous Batches as see Batchdate col in Batches)then Used Cursor for that 5 Pins and save this into Transactions (insert 1 transaction) and against of this Transactions PinIDs goet to TransactionLineItems (5 PinIDs goes to TransactionLineItems and Generate 1 Transaction for wholeQuantity)
plz reply me as i m not able to make this SP with Cursors as i have no idea abt how to used cursors. so plz help me
Thanx in Advance.
December 18, 2006 at 10:16 am
Simple cursor example you can use as a guide:
http://www.databasejournal.com/features/mssql/article.php/3430251
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply