September 26, 2003 at 12:53 pm
We've got an app that was recently built by newbie SQL programmers. The app takes a check and applies portions of this check to certain accounts within the database. They have a SPROC that does the INSERT (one INSERT at a time). So, when one check has more than one account, this SPROC is executed n times until all accounts are INSERTED. As a DBA, this method seems REALLY inefficient. I would think you would collect all the account information specific to the check, THEN pass this information to the database, and execute one INSERT statement. I mean, what about transactional control? Where's the ROLLBACK possibilities in the way they are doing this?
What is the best way to do this?
Thanks,
September 26, 2003 at 1:44 pm
There is actually nothing WRONG with this method except for the transactional issues raised in your post article. That issue is easily address in code by using MTS or any transactional engine.
The question of efficency is raised by repeated calls to the database, however. If the rules determining the individual deposits can be stored in the database, then you have a reasonable opportunity to complete the insert(s) in a single sproc call. The question of transactional space can then be addressed in the procedure AND code for additional assurances.
The best approach MAY be to use a cursor. Once again, the rules governing the distribution of the deposit must be available to the stored procedure.
For example, if the total deposit is to be distributed to four accounts in this way:
Total Deposit: $100.01
Account A: 30%
Account B: 22%
Account C: 18%
Account D: 30%
You would need some way to JOIN these percentages to the deposit. You must also ensure that the entire amount is distributed. This is likely why the code is performed outside of SQL Server. That being said and with the understanding that I've never seen your schema, here is a possible solution:
CREATE PROCEDURE DISTRIBUTE_DEPOSIT
(
@ACCT_ID INT,
@DEPOSIT_AMT MONEY
)
AS
BEGIN
DECLARE @DEPOSIT_PERCENTAGE_RESULT MONEY,
@SUB_ACCT_ID INT,
@REMAINDER MONEY,
@ACTUAL_DEPOSIT MONEY,
@EXPECTED_REMAINDER MONEY,
@EXPECTED_DISTRIBUTED_AMT MONEY
-- FIRST, DETERMINE IF THERE WILL BE A REMAINDER IF
-- THE DEPOSIT IS DISTRIBUTED ACCORDING TO BUSINESS RULES
SET @EXPECTED_DISTRIBUTED_AMT =
(
SELECT SUM(ACCT.PERCENTAGE * @DEPOSIT_AMT)
FROM SUB_ACCOUNTS ACCT
WHERE ACCT.MASTER_ACCT_ID = @ACCT_ID
)
-- IF THIS AMOUNT DOES NOT MATCH THE TOTAL,
-- SOME OF THE BALANCE WILL NOT BE APPLIED WHEN THE SUB-DEPOSITS ARE CREATED
-- FIND OUT IF THERE IS A REMAINDER
SET @EXPECTED_REMAINDER = (@DEPOSIT_AMT - @EXPECTED_DISTRIBUTED_AMT)
-- WE NEED TO ACCOUNT FOR THIS REMAINDER
DECLARE CUR_DIST_DEPOSIT CURSOR
LOCAL FAST_FORWARD FOR
SELECT ACCT.PERCENTAGE * @DEPOSIT_AMT,
ACCT.SUB_ACCT_ID
FROM SUB_ACCOUNTS ACCT
WHERE ACCT.MASTER_ACCT_ID = @ACCT_ID
ORDER BY ACCT.PERCENTAGE DESC
-- the order by clause gives weight to accounts intended
-- to receive greater share
DECLARE @DEPOSIT_PERCENTAGE_RESULT MONEY,
@SUB_ACCT_ID INT,
@REMAINDER MONEY,
@ACTUAL_DEPOSIT MONEY
-- START WITH THE TOTAL DEPOSIT
SET @REMAINDER = @DEPOSIT_AMT
BEGIN TRANSACTION
OPEN CUR_DIST_DEPOSIT
FETCH CUR_DIST_DEPOSIT INTO
@DEPOSIT_PERCENTAGE_RESULT,
@SUB_ACCT_ID
WHILE @@FETCH_STATUS <> 0
BEGIN
-- MAKE SURE THERE IS ENOUGH FOR THE EXPECTED DEPOSIT AMOUNT
IF @EXPECTED_REMAINDER <> 0
BEGIN
-- THIS WORKS WHETHER OR NOT RESULT IS POSITIVE
SET @THIS_DEPOSIT = (@DEPOSIT_PERCENTAGE_RESULT + @EXPECTED_REMAINDER)
SET @EXPECTED_REMAINDER = 0 -- WE'VE ACCOUNTED FOR IT
END
ELSE
BEGIN
SET @THIS_DEPOSIT = @DEPOSIT_PERCENTAGE_RESULT
END
INSERT INTO SUB_ACCOUNT_DEPOSITS
(
AMT,
SUB_ACCT_ID
)
VALUES
(
@THIS_DEPOSIT,
@SUB_ACCT_ID
)
@REMAINDER = @REMAINDER - @THIS_DEPOSIT
FETCH CUR_DIST_DEPOSIT INTO
@DEPOSIT_PERCENTAGE_RESULT,
@SUB_ACCT_ID
END
CLOSE CUR_DIST_DEPOSIT
DEALLOCATE CUR_DIST_DEPOSIT
IF @REMAINDER <> 0
BEGIN
RAISERROR('COULD NOT DISTRIBUTE ALL FUNDS',16,0)
GOTO EXIT_WITH_ROLLBACK
END
ELSE
BEGIN
COMMIT TRANSACTION
GOTO EXIT_WITH_COMMIT
END
EXIT_WITH_COMMIT:
IF @@TRANCOUNT > 0 COMMIT TRANSACTION
GOTO EXIT_PROC
EXIT_WITH_ROLLBACK:
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
GOTO EXIT_PROC
EXIT_PROC:
END
It is a privilege to see so much confusion. -- Marianne Moore, The Steeplejack
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply