Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: SQL - Loop Problem

    Morning all,

    I was wondering if someone could help me here.

    The loop only seems to be running the EXEC for @x = 2. Any ideas? I wasn't sure if the EXEC function can only be run once.

    I will also post the td_Credit_Control_Install_SelectRules stored proc.

    Many thanks,
    -TJD

    td_Credit_Control_Install_AddRules

    Code:
    CREATE PROCEDURE td_Credit_Control_Install_AddRules
    @BranchID int
    
    AS
    DECLARE @MinSourceID int, @MaxSourceIDs int, @x int
    
    SET @MinSourceID = 2
    SET @MaxSourceIDs = (SELECT ISNULL(MAX(pfinstalments_result_id) + 1, 1) FROM PFInstalments_Result)
    WHILE @MinSourceID < @MaxSourceIDs
    BEGIN
    	SET @x = @MinSourceID
    	DECLARE @d VARCHAR(255), @d1 VARCHAR(255), @d2 VARCHAR(255)		
    	SET @d1 = (SELECT description FROM pfinstalments_result WHERE pfinstalments_result_id = @MinSourceID)
    	SET @d2 = (SELECT code FROM pfinstalments_result WHERE pfinstalments_result_id = @MinSourceID) 
    	SET @d = 'Bounce ' + @d2 + ' - ' + @d1
    	IF NOT EXISTS (SELECT description, source_id FROM Credit_Control_Rule WHERE description = @d AND source_id = @BranchID)	
    
    	INSERT INTO Credit_Control_Rule (description, source_id, business_type, pffrequency_id, is_active, processing_days, 
    	pfinstalments_result_id) VALUES (@d, @BranchID, 'INS', 3, 1, 0, @MinSourceID) 
    	EXEC td_Credit_Control_Install_SelectRules @SourceID = @x
    
    	SET @MinSourceID = @MinSourceID + 1	
    END
    GO

    td_Credit_Control_Install_SelectRules

    Code:
    CREATE PROCEDURE td_Credit_Control_Install_SelectRules
    @SourceID int
    
    AS
    
    IF @SourceID = 2
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_1
    	END
    ELSE IF @SourceID = 14 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_2
    	END
    ELSE IF @SourceID = 15 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_3
    	END
    ELSE IF @SourceID = 16 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_4
    	END
    ELSE IF @SourceID = 17 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_5
    	END
    ELSE IF @SourceID = 18 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_6
    	END
    ELSE IF @SourceID = 19 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_7		
    	END
    ELSE IF @SourceID = 20 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_8
    	END
    ELSE IF @SourceID = 21 
    	BEGIN
    		EXEC td_Credit_Control_Install_AddSteps_9	
    	END
    ELSE IF @SourceID = 22 
    	BEGIN	
    		EXEC td_Credit_Control_Install_AddSteps_10		
    	END
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    please post some sample data available in the tables referred in the sps


    thanks,

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    I can't at the moment as the server is down. I will post as soon as I can re-run the script.

    I was hoping something would stand out in the meantime.

    Many thanks.

  4. #4
    Join Date
    Apr 2008
    Posts
    4

    Unhappy Results

    Ok, server back up. Hope this helps.



    The output of the Credit_Control_Rules table, affected by the td_Credit_Control_Install_AddRules procedure.

    Code:
    credit_control_rule_id	description	source_id	business_type	pffrequency_id	is_active	processing_days	pfinstalments_result_id
    0	Credit Control Rule 1	2	NB		1	1	
    253	Bounce 0 - Refer To Payer	2	INS	3	1	0	2
    254	Bounce 1 - Instruction Cancelled	2	INS	3	1	0	3
    255	Bounce 2 - Payer Deceased	2	INS	3	1	0	4
    256	Bounce 3 - Account Transferred	2	INS	3	1	0	5
    257	Bounce 4 - Advance Notice Disputed	2	INS	3	1	0	6
    258	Bounce 5 - No Account	2	INS	3	1	0	7
    259	Bounce 6 - No Instruction	2	INS	3	1	0	8
    260	Bounce 7 - Amount Differs	2	INS	3	1	0	9
    261	Bounce 8 - Amount Not Due	2	INS	3	1	0	10
    262	Bounce 9 - Presentation Overdue	2	INS	3	1	0	11
    263	Bounce A - Originator Differs	2	INS	3	1	0	12
    264	Bounce B - Account Closed	2	INS	3	1	0	13
    265	Bounce 0 - Refer To Payer	3	INS	3	1	0	2
    266	Bounce 1 - Instruction Cancelled	3	INS	3	1	0	3
    267	Bounce 2 - Payer Deceased	3	INS	3	1	0	4
    268	Bounce 3 - Account Transferred	3	INS	3	1	0	5
    269	Bounce 4 - Advance Notice Disputed	3	INS	3	1	0	6
    270	Bounce 5 - No Account	3	INS	3	1	0	7
    271	Bounce 6 - No Instruction	3	INS	3	1	0	8
    272	Bounce 7 - Amount Differs	3	INS	3	1	0	9
    273	Bounce 8 - Amount Not Due	3	INS	3	1	0	10
    274	Bounce 9 - Presentation Overdue	3	INS	3	1	0	11
    275	Bounce A - Originator Differs	3	INS	3	1	0	12
    276	Bounce B - Account Closed	3	INS	3	1	0	13
    277	Bounce 0 - Refer To Payer	4	INS	3	1	0	2
    278	Bounce 1 - Instruction Cancelled	4	INS	3	1	0	3
    279	Bounce 2 - Payer Deceased	4	INS	3	1	0	4
    280	Bounce 3 - Account Transferred	4	INS	3	1	0	5
    281	Bounce 4 - Advance Notice Disputed	4	INS	3	1	0	6
    282	Bounce 5 - No Account	4	INS	3	1	0	7
    283	Bounce 6 - No Instruction	4	INS	3	1	0	8
    284	Bounce 7 - Amount Differs	4	INS	3	1	0	9
    285	Bounce 8 - Amount Not Due	4	INS	3	1	0	10
    286	Bounce 9 - Presentation Overdue	4	INS	3	1	0	11
    287	Bounce A - Originator Differs	4	INS	3	1	0	12
    288	Bounce B - Account Closed	4	INS	3	1	0	13
    289	Bounce 0 - Refer To Payer	5	INS	3	1	0	2
    290	Bounce 1 - Instruction Cancelled	5	INS	3	1	0	3
    291	Bounce 2 - Payer Deceased	5	INS	3	1	0	4
    292	Bounce 3 - Account Transferred	5	INS	3	1	0	5
    293	Bounce 4 - Advance Notice Disputed	5	INS	3	1	0	6
    294	Bounce 5 - No Account	5	INS	3	1	0	7
    295	Bounce 6 - No Instruction	5	INS	3	1	0	8
    296	Bounce 7 - Amount Differs	5	INS	3	1	0	9
    297	Bounce 8 - Amount Not Due	5	INS	3	1	0	10
    298	Bounce 9 - Presentation Overdue	5	INS	3	1	0	11
    299	Bounce A - Originator Differs	5	INS	3	1	0	12
    300	Bounce B - Account Closed	5	INS	3	1	0	13
    301	Bounce 0 - Refer To Payer	6	INS	3	1	0	2
    
    (50 row(s) affected)
    The output of the Credit_Control_Rules table, affected by the td_Credit_Control_Install_SelectRules procedure.

    Code:
    credit_control_step_id	credit_control_rule_id	step_description
    
    0	0	First reminder letter
    85	253	Bounce - OB - Step 1 - Bounce Letter Sent
    86	253	Bounce - OB - Step 2 - Reminder Letter Sent
    87	253	Bounce - OB - Step 3 - Cancellation Letter Sent
    88	253	Bounce - OB - Step 4 - Policy Cancelled
    89	265	Bounce - OB - Step 1 - Bounce Letter Sent
    90	265	Bounce - OB - Step 2 - Reminder Letter Sent
    91	265	Bounce - OB - Step 3 - Cancellation Letter Sent
    92	265	Bounce - OB - Step 4 - Policy Cancelled
    93	277	Bounce - OB - Step 1 - Bounce Letter Sent
    94	277	Bounce - OB - Step 2 - Reminder Letter Sent
    95	277	Bounce - OB - Step 3 - Cancellation Letter Sent
    96	277	Bounce - OB - Step 4 - Policy Cancelled
    97	289	Bounce - OB - Step 1 - Bounce Letter Sent
    98	289	Bounce - OB - Step 2 - Reminder Letter Sent
    99	289	Bounce - OB - Step 3 - Cancellation Letter Sent
    100	289	Bounce - OB - Step 4 - Policy Cancelled
    101	301	Bounce - OB - Step 1 - Bounce Letter Sent
    102	301	Bounce - OB - Step 2 - Reminder Letter Sent
    103	301	Bounce - OB - Step 3 - Cancellation Letter Sent
    104	301	Bounce - OB - Step 4 - Policy Cancelled
    105	313	Bounce - OB - Step 1 - Bounce Letter Sent
    106	313	Bounce - OB - Step 2 - Reminder Letter Sent
    107	313	Bounce - OB - Step 3 - Cancellation Letter Sent
    108	313	Bounce - OB - Step 4 - Policy Cancelled
    109	325	Bounce - OB - Step 1 - Bounce Letter Sent
    110	325	Bounce - OB - Step 2 - Reminder Letter Sent
    111	325	Bounce - OB - Step 3 - Cancellation Letter Sent
    112	325	Bounce - OB - Step 4 - Policy Cancelled
    113	337	Bounce - OB - Step 1 - Bounce Letter Sent
    114	337	Bounce - OB - Step 2 - Reminder Letter Sent
    115	337	Bounce - OB - Step 3 - Cancellation Letter Sent
    116	337	Bounce - OB - Step 4 - Policy Cancelled
    117	349	Bounce - OB - Step 1 - Bounce Letter Sent
    118	349	Bounce - OB - Step 2 - Reminder Letter Sent
    119	349	Bounce - OB - Step 3 - Cancellation Letter Sent
    120	349	Bounce - OB - Step 4 - Policy Cancelled
    121	361	Bounce - OB - Step 1 - Bounce Letter Sent
    122	361	Bounce - OB - Step 2 - Reminder Letter Sent
    123	361	Bounce - OB - Step 3 - Cancellation Letter Sent
    124	361	Bounce - OB - Step 4 - Policy Cancelled
    125	373	Bounce - OB - Step 1 - Bounce Letter Sent
    126	373	Bounce - OB - Step 2 - Reminder Letter Sent
    127	373	Bounce - OB - Step 3 - Cancellation Letter Sent
    128	373	Bounce - OB - Step 4 - Policy Cancelled
    129	385	Bounce - OB - Step 1 - Bounce Letter Sent
    130	385	Bounce - OB - Step 2 - Reminder Letter Sent
    131	385	Bounce - OB - Step 3 - Cancellation Letter Sent
    132	385	Bounce - OB - Step 4 - Policy Cancelled
    133	397	Bounce - OB - Step 1 - Bounce Letter Sent
    134	397	Bounce - OB - Step 2 - Reminder Letter Sent
    135	397	Bounce - OB - Step 3 - Cancellation Letter Sent
    136	397	Bounce - OB - Step 4 - Policy Cancelled
    137	409	Bounce - OB - Step 1 - Bounce Letter Sent
    138	409	Bounce - OB - Step 2 - Reminder Letter Sent
    139	409	Bounce - OB - Step 3 - Cancellation Letter Sent
    140	409	Bounce - OB - Step 4 - Policy Cancelled
    141	421	Bounce - OB - Step 1 - Bounce Letter Sent
    142	421	Bounce - OB - Step 2 - Reminder Letter Sent
    143	421	Bounce - OB - Step 3 - Cancellation Letter Sent
    144	421	Bounce - OB - Step 4 - Policy Cancelled
    145	433	Bounce - OB - Step 1 - Bounce Letter Sent
    146	433	Bounce - OB - Step 2 - Reminder Letter Sent
    147	433	Bounce - OB - Step 3 - Cancellation Letter Sent
    148	433	Bounce - OB - Step 4 - Policy Cancelled
    149	445	Bounce - OB - Step 1 - Bounce Letter Sent
    150	445	Bounce - OB - Step 2 - Reminder Letter Sent
    151	445	Bounce - OB - Step 3 - Cancellation Letter Sent
    152	445	Bounce - OB - Step 4 - Policy Cancelled
    153	457	Bounce - OB - Step 1 - Bounce Letter Sent
    154	457	Bounce - OB - Step 2 - Reminder Letter Sent
    155	457	Bounce - OB - Step 3 - Cancellation Letter Sent
    156	457	Bounce - OB - Step 4 - Policy Cancelled
    157	469	Bounce - OB - Step 1 - Bounce Letter Sent
    158	469	Bounce - OB - Step 2 - Reminder Letter Sent
    159	469	Bounce - OB - Step 3 - Cancellation Letter Sent
    160	469	Bounce - OB - Step 4 - Policy Cancelled
    161	481	Bounce - OB - Step 1 - Bounce Letter Sent
    162	481	Bounce - OB - Step 2 - Reminder Letter Sent
    163	481	Bounce - OB - Step 3 - Cancellation Letter Sent
    164	481	Bounce - OB - Step 4 - Policy Cancelled
    165	493	Bounce - OB - Step 1 - Bounce Letter Sent
    166	493	Bounce - OB - Step 2 - Reminder Letter Sent
    167	493	Bounce - OB - Step 3 - Cancellation Letter Sent
    168	493	Bounce - OB - Step 4 - Policy Cancelled
    
    (85 row(s) affected)

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I would start by putting print @variable after every variable in the script / while loop, it may not be doing what you think it is.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hello?

    Ever hear of set based processing?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    but, that is an excellent display of code reuse nonetheless. that set based stuff is just so ugly to look at and this is so compartmentalized and easy to read and it is self documenting code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Apr 2008
    Posts
    4

    Smile

    Brett - No. I haven't lol. I don't do SQL often. My excuse is that I was taught Pascal. If you can read it, and it makes sense, then it should work.

    Thanks for all your help guys. I did get round to using the Print variable. Found out that the problem was in the td_Credit_Control_Install_AddRules
    stored proc. I was using the @MinSourceID (as @x) as the variable, when I needed it to repeat for each branch. Problem solved.

    Code:
    EXEC td_Credit_Control_Install_SelectRules @SourceID = @x
    Code:
    EXEC td_Credit_Control_Install_SelectRules @SourceID = @BranchID
    Cheers,
    -TJD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •