• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Cursor Syntax Error

I am setting the cursor from below, and I am getting the following error: "Incorrect Syntax near 'END'". Can you please tell me what I am doing wrong with the syntax of my cursor?


-----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
--DECLARE VARIABLES TO BE USED WITHIN THE CURSOR
DECLARE @claim_num [numeric](18, 0),
        @claim_page_num [varchar](10),
            @Counter [numeric] (18, 0),
        @Record_Count [numeric] (18,0)

--COUNTERS ARE SET TO FORCE CURSOR INTO LOOP UNTIL ALL RECORDS ARE PROCESSED
SET @Counter = 0
SET @Record_Count = (select count(*) from #cursor_trans)

DECLARE claim_credit_cursor CURSOR
FOR SELECT * FROM #cursor_trans

OPEN claim_credit_cursor

FETCH NEXT FROM claim_credit_cursor
INTO @claim_num, @claim_page_num

/*
---------------------------------------------
select *
from #br20
where claim_num = 5508064
and claim_page_num = 1

select *
from #cursor_trans
where claim_num = 5508064
and claim_page_num = 1

select *
from dia_focus_adjustment_type
where adjustment_type_num = 23

select *
from #credit_results
--------------------------------------------------
*/

--INITIATES THE LOOP THE CURSOR WILL PROCESS THROUGH
WHILE @Record_Count <> @Counter
BEGIN

IF (select count(*) from #credit_results
      where @claim_num = #credit_results.claim_num
      and @claim_page_num = #credit_results.claim_page_num) = 0

---------------------------------------------------------
---------------------------------------------------------
BEGIN
      INSERT INTO #credit_results
      SELECT DISTINCT #br20.claim_num, #br20.claim_page_num
      FROM #br20, #cursor_trans, dia_focus_adjustment_type
      WHERE #br20.claim_num = #cursor_trans.claim_num
      AND #br20.claim_page_num = #cursor_trans.claim_page_num
      AND (#br20.current_balance + #cursor_trans.transaction_amt) NOT LIKE '%-%'
      AND #cursor_trans.adjustment_type_num = dia_focus_adjustment_type.adjustment_type_num
      AND substring(dia_focus_adjustment_type.adjustment_type,1,1) NOT like '%Y%'
      AND dia_focus_adjustment_type.gl_code_num <> '682'
      AND dia_focus_adjustment_type.adjustment_type_num not in ('4','5')
--5508064
END
ELSE
---------------------------------------------------------
---------------------------------------------------------

IF (select count(*) from #credit_results
      where @claim_num = #credit_results.claim_num
      and @claim_page_num = #credit_results.claim_page_num) = 1
BEGIN
      SET @Counter = @Counter + 1
END


---------------------------------------------------------
---------------------------------------------------------
FETCH NEXT FROM claim_credit_cursor
INTO @claim_num, @claim_page_num

END

---------------------------------------------------------
---------------------------------------------------------
--CLOSE CURSOR RUN
CLOSE claim_credit_cursor
DEALLOCATE claim_credit_cursor

END

0
nf38453
Asked:
nf38453
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
remove that last 'End'

--DECLARE VARIABLES TO BE USED WITHIN THE CURSOR
DECLARE @claim_num [numeric](18, 0),
        @claim_page_num [varchar](10),
            @Counter [numeric] (18, 0),
        @Record_Count [numeric] (18,0)

--COUNTERS ARE SET TO FORCE CURSOR INTO LOOP UNTIL ALL RECORDS ARE PROCESSED
SET @Counter = 0
SET @Record_Count = (select count(*) from #cursor_trans)

DECLARE claim_credit_cursor CURSOR
FOR SELECT * FROM #cursor_trans

OPEN claim_credit_cursor

FETCH NEXT FROM claim_credit_cursor
INTO @claim_num, @claim_page_num 

/*
---------------------------------------------
select *
from #br20
where claim_num = 5508064
and claim_page_num = 1

select *
from #cursor_trans
where claim_num = 5508064
and claim_page_num = 1

select *
from dia_focus_adjustment_type
where adjustment_type_num = 23

select *
from #credit_results
-------------------------------------------------- 
*/

--INITIATES THE LOOP THE CURSOR WILL PROCESS THROUGH
WHILE @Record_Count <> @Counter
BEGIN

	IF (select count(*) from #credit_results 
		  where @claim_num = #credit_results.claim_num
		  and @claim_page_num = #credit_results.claim_page_num) = 0 

	---------------------------------------------------------
	---------------------------------------------------------
	BEGIN
		  INSERT INTO #credit_results
		  SELECT DISTINCT #br20.claim_num, #br20.claim_page_num 
		  FROM #br20, #cursor_trans, dia_focus_adjustment_type
		  WHERE #br20.claim_num = #cursor_trans.claim_num
		  AND #br20.claim_page_num = #cursor_trans.claim_page_num
		  AND (#br20.current_balance + #cursor_trans.transaction_amt) NOT LIKE '%-%'
		  AND #cursor_trans.adjustment_type_num = dia_focus_adjustment_type.adjustment_type_num
		  AND substring(dia_focus_adjustment_type.adjustment_type,1,1) NOT like '%Y%'
		  AND dia_focus_adjustment_type.gl_code_num <> '682'
		  AND dia_focus_adjustment_type.adjustment_type_num not in ('4','5')
	--5508064 
	END
	ELSE
	---------------------------------------------------------
	---------------------------------------------------------

	IF (select count(*) from #credit_results 
		  where @claim_num = #credit_results.claim_num
		  and @claim_page_num = #credit_results.claim_page_num) = 1 
	BEGIN
		  SET @Counter = @Counter + 1
	END 


	---------------------------------------------------------
	---------------------------------------------------------
	FETCH NEXT FROM claim_credit_cursor
	INTO @claim_num, @claim_page_num 

END

---------------------------------------------------------
---------------------------------------------------------
--CLOSE CURSOR RUN
CLOSE claim_credit_cursor
DEALLOCATE claim_credit_cursor

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now