July 27, 2011 at 12:20 pm
Table Structure: -----
a) Account (This table stores the Account Information. One customer can have more than one account)
1.AccountNo nchar(20)
2.CustomerID char(10)
3.AccountType nchar(20)
4.DateOfOpening datetime
5.Amount money
6.Status (C, F) Status indicating whether the account is closed or
functional
b) Transaction (This table stores the details of each transaction)
TransactionID (Auto, Use Identity Property) int
AccountNo nchar(20)
DateOfTransaction datetime
Amount money
TransactionType (D, W) Deposit, Withdraw
Description varchar(50)
Create a Stored Procedure/Function for Account Transaction
The SP should accept AccountNo, Amount, TransactionType and Description and insert them into the AccountTransaction table. The field Transaction Date, should be the system date. This insertion should happen only if the following conditions are met.
i) The AccountNo should exist.
ii) The Amount should be a positive number.
iii) The Type of transaction should be valid.
iv) If the Type is ‘W’(WithDraw), sufficient amount should be available for the transaction. That is, the balance amount should be greater than or equal to 500 after withdrawal.
The SP should also update CurrentAmount in the Account table. The SP should return an integer as explained below
i) 1, if the AccountNo is invalid.
ii) 2, if the Amount is not a positive number.
iii) 3, if the fund is not sufficient.
iv) 4, if the transaction is successful.
v) 0, if transaction failed.
IS THIS STORED PROCEDURE CORRECT ?
CREATE PROC [dbo].[procADD](@AccountNo NCHAR(20), @Amount MONEY, @TransactionType CHAR(1), @Description VARCHAR(50)) AS
SET XACT_ABORT ON
IF @Amount < 0 BEGIN
RETURN 2 --amount is negative
END
IF @TransactionType NOT IN ('W','D') BEGIN --you might want to make a foreign key table for this instead of hardcoding it
RETURN 0 --didn't specify an error code
END
IF EXISTS(SELECT * FROM Account WHERE [AccountNo]=@AccountNo) BEGIN
RETURN 1 --no account number
END
IF @TransactionType = 'W' BEGIN
BEGIN TRANSACTION
UPDATE Account SET [Amount]=[Amount]-@Amount WHERE [AccountNo]=@AccountNo AND [Amount]-@Amount < 500
INSERT INTO dbo.[Transaction] (AccountNo, DateOfTransaction, Amount,TransactionType,[Description]) VALUES (@AccountNo,GETDATE(),@Amount,@TransactionType,@Description)
IF @@ROWCOUNT = 0 BEGIN
ROLLBACK TRANSACTION
RETURN 3 --insufficient funds
END
ELSE BEGIN
COMMIT TRANSACTION
RETURN 4 --successful
END
END
ELSE BEGIN --Transaction type must be D
BEGIN TRANSACTION
UPDATE Account SET [Amount]=[Amount]+@Amount WHERE [AccountNo]=@AccountNo
INSERT INTO dbo.[Transaction] (AccountNo, DateOfTransaction, Amount,TransactionType,[Description]) VALUES (@AccountNo,GETDATE(),@Amount,@TransactionType,@Description)
COMMIT TRANSACTION
RETURN 4
END
THANKS .
[font="Arial"][/font]
July 27, 2011 at 12:54 pm
not bad to, good job posting your work. since you put some effort into the homework problem , let me try to offer some constructive advice.
i think i see two issues.
in one spot you have code like this:
IF EXISTS(SELECT * FROM Account WHERE [AccountNo]=@AccountNo)
BEGIN
RETURN 1 --no account number
END
i *think* you were testing to make sure the account # is valid. but the code you are using says "if the account number exists, RETURN (and do no more work)
you probably wantedIF NOT EXISTS.....
the second thing i'd as is where you are deciding this:
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION
RETURN 3 --insufficient funds
END
you *seem* to want to detect an error for insufficient funds, but selecting the @@ROWCOUNT immediately after a insert does not test anything at all related to insufficient funds.
You definitely need to fix that logic.
How would you test for insufficient funds before doing the actual insert and update?
good luck!
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply