quote:

---

Msg 102, Level 15, State 1, Procedure sp_MakeSelheadUpdates, Line 80
Incorrect syntax near 'END'.

---

quote:

---

However, should a stored procedure always end with an END statement.

---

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ownr_cd = 'TTX' OR @ownr_cd = 'GONX')

IF EXISTS( SELECT * FROM SELHEAD WHERE SELHEAD_ACCT_DATE = @acct_date and SELHEAD_COMPANY = @ownr_cd and SELHEAD_BILLING_ROAD = @bill_rd )
SET @rec_found = 'Y'


IF EXISTS (SELECT * FROM OCR_CLIENT_CODE WHERE OCR_CLIENT_INIT =@ownr_cd)
SET @rec_found = 'Y'

END

ALTER PROCEDURE [TTXDOM01\devayb].[sp_MakeSelheadUpdates] 
	-- ADD the parameters FOR the stored PROCEDURE here
	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets FROM
	-- interfering WITH SELECT statements.
	SET NOCOUNT ON;
	DECLARE @STR varchar(50)
	SET @STR = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
	DECLARE @ownr_cd CHAR(4)
	, @acct_date  datetime
	, @bill_rd CHAR(4)
	, @bill_rd_sffx decimal(2,0)
	, @ltr_stat CHAR(1)
	, @tot_cba_amt decimal(9,2)
	, @excp_close_dt datetime
	, @selhead_excp_status CHAR(1)
	, @rec_found CHAR(1)
	
	DECLARE db_cursor cursor READ_ONLY FORWARD_ONLY LOCAL  FOR
	SELECT 	RA_EXCP_ACCT_DATE
		,RA_EXCP_OWNR_MRK_CD
		,RA_EXCP_BILL_RD
		,RAEXCP_BILL_RD_SFFX
		,RAEXCP_LTR_STAT
		,RAEXCP_TOTL_CBA_AMT
		,RAEXCP_EXCP_CLOSE_DT 
	FROM 	STG_R6100MU_RA_EXCP
	
	OPEN db_cursor   
	FETCH NEXT FROM db_cursor INTO @acct_date,@ownr_cd,@bill_rd,@bill_rd_sffx,@ltr_stat,@tot_cba_amt,@excp_close_dt  

	WHILE @@FETCH_STATUS = 0   
	BEGIN   
		IF (@ownr_cd = 'TTX' OR @ownr_cd = 'GONX')
			IF EXISTS( SELECT * FROM SELHEAD WHERE SELHEAD_ACCT_DATE = @acct_date AND SELHEAD_COMPANY = @ownr_cd AND SELHEAD_BILLING_ROAD = @bill_rd )
				SET @rec_found = 'Y'
				
			   
		IF EXISTS (SELECT * FROM OCR_CLIENT_CODE WHERE OCR_CLIENT_INIT =@ownr_cd)
			SET @rec_found = 'Y'
			   
		IF @rec_found ='Y'
		BEGIN
			IF (@ltr_stat =  'W'OR @Ltr_stat = 'M' OR @ltr_stat = 'X')
			BEGIN	-- <==
				SET @selhead_excp_status = 'C'
			END
					
			IF @ltr_stat = 'L'
			BEGIN	-- <==
				SET @selhead_excp_status = 'P'
			END
				     
			UPDATE 	SELHEAD 
			SET 	SELHEAD_EXCEPTION_STATUS = @selhead_excp_status 
				,SELHEAD_TOT_CBA_TODATE = @tot_cba_amt
				,SELHEAD_DATE_CLOSED = @excp_close_dt
				,SELHEAD_LAST_LOG_UPDT_BY = 342
--				,SELHEAD_LAST_LOG_UPDT_ON
			WHERE 	SELHEAD_ACCT_DATE = @acct_date AND SELHEAD_COMPANY = @ownr_cd AND SELHEAD_BILLING_ROAD = @bill_rd 
			      
		END ---END OF IF @REC_FOUND ='Y'
			     
		FETCH NEXT FROM db_cursor INTO @acct_date,@ownr_cd,@bill_rd,@bill_rd_sffx,@ltr_stat,@tot_cba_amt,@excp_close_dt
				
	END 

	CLOSE db_cursor   
	DEALLOCATE db_cursor 
		
END

quote:

---

Originally posted by saiabhiram

Thanks so much. It works.

---

ALTER PROCEDURE [TTXDOM01\devayb].[sp_MakeSelheadUpdates] 
AS
BEGIN
	
	UPDATE 	a
	SET 	SELHEAD_EXCEPTION_STATUS 	= CASE 	WHEN b.RAEXCP_LTR_STAT IN ('W', 'M', 'X')
							THEN 'C'
							WHEN b.RAEXCP_LTR_STAT IN ('L')
							THEN 'P'
							END
		,SELHEAD_TOT_CBA_TODATE 	= b.RAEXCP_TOTL_CBA_AMT
		,SELHEAD_DATE_CLOSED 		= b.RAEXCP_EXCP_CLOSE_DT
		,SELHEAD_LAST_LOG_UPDT_BY 	= 342
	FROM	SELHEAD a
		INNER JOIN STG_R6100MU_RA_EXCP b	ON	s.SELHEAD_ACCT_DATE	= b.RA_EXCP_ACCT_DATE
							AND	s.SELHEAD_COMPANY	= b.RA_EXCP_OWNR_MRK_CD
							AND	s.SELHEAD_BILLING_ROAD	= b.RA_EXCP_BILL_RD
	WHERE	b.RA_EXCP_OWNR_MRK_CD	IN ( 'TTX', 'GONX' )
	OR	EXISTS		-- not sure should the condition be OR. 
		(
			SELECT 	* 
			FROM 	OCR_CLIENT_CODE x
			WHERE 	x.OCR_CLIENT_INIT = b.RA_EXCP_OWNR_MRK_CD
		)

END