SET NOCOUNT ON

DECLARE ...

-- append a separator at the end of the array
SET @compNums = @compNums + @separator
SET @frequencies = @frequencies + @separator
SET @duedates = @duedates + @separator
SET @cycledates = @cycledates + @separator

while patindex('%' + @separator + '%' , @compNums) <> 0
BEGIN
	select @separator_comp = patindex('%' + @separator + '%' , @compNums)
	select @separator_date = patindex('%' + @separator + '%' , @duedates)
	select @separator_freq = patindex('%' + @separator + '%' , @frequencies)
	select @separator_cycle = patindex('%' + @separator + '%' , @cycledates)	
	
	--get the member element
	select @compNum = left(@compNums , @separator_comp - 1)
	select @duedate = left(@duedates , @separator_date - 1)
	select @freq = left(@frequencies , @separator_freq - 1)
	select @cycle = left(@cycledates , @separator_cycle - 1)
		
	SET @bookexists = NULL
	-- check if PDF request exists in AutoRun for company
	SELECT @bookexists = 1 FROM dbo.AutoRun WHERE CompNum = @compNum AND FileType = 'PDFSTMT'
	
	IF @stmt = 1		--stmt requested
		BEGIN
		IF @bookexists IS NULL		--record doesn't exist in AutoRun table
			BEGIN
			IF @duedate <> '' OR @duedate = '0'
				BEGIN

				BEGIN TRANSACTION

				INSERT INTO dbo.AutoRun(ICN, FileType, CompNum, Frequency, DueDate)
				VALUES (@icn, 'PDFSTMT', @compNum, @freq, @duedate)

				IF @@ERROR != 0 GOTO ERROR_HANDLER

				Update dbo.CompNames
				Set AutomateStmts = 1
				Where CompNum = @compNum AND InternetNumber = @icn

				IF @@ERROR != 0 GOTO ERROR_HANDLER

				IF @freq = 'CDPLUS'
					BEGIN
					--if submission (today) is past the cycle date
					IF (SELECT DATEDIFF(d, GETDATE(), (@cd))) <= 0
						BEGIN
							--if today's date is less than or equal to the cycle date + duedate, insert or update record in FileAdmin table with the rundate as cycledate+duedate
							IF (SELECT DATEDIFF(d,GETDATE(),(@cd + CAST(@duedate as int)))) >= 0
							BEGIN
								--if record exists in FileAdmin, update rundate
								IF Exists (Select 1 from dbo.Fileadmin Where txtCompanyNum = @compNum And blnExported = 0 And txtFileType = 'PDFSTMT' And blnAutomatedRequest = 1)
									BEGIN
									Update dbo.FileAdmin
									Set dteRunDate = @rundate
									Where txtCompanyNum = @compNum And blnExported = 0 And txtFileType = 'PDFSTMT' And blnAutomatedRequest = 1

									IF @@ERROR != 0 GOTO ERROR_HANDLER
									END
								ELSE	-- record doesn't exist in FileAdmin, do an insert
									BEGIN
									INSERT INTO dbo.FileAdmin (txtCompanyNum, txtAddOpt, dteCycleDate, dteLastUsed, blnExported, txtInternetNum, txtStatus, dteRunDate, txtFileType, blnAutomatedRequest )
									VALUES (@compNum, '', @cd, GETDATE(), 0, @icn, '',  @rundate , 'PDFSTMT', 1 )				

									IF @@ERROR != 0 GOTO ERROR_HANDLER
									END
							END
						END
					END
                                COMMIT TRANSACTION

				END
			END
		ELSE		--record already exists in AutoRun table
			BEGIN
			IF @duedate <> '' Or @duedate = '0'
				BEGIN
				
				BEGIN TRANSACTION

				UPDATE dbo.AutoRun
				SET DueDate = CAST(@duedate as int), Frequency = @freq
				WHERE CompNum = @compNum AND FileType = 'PDFSTMT'

				IF @@ERROR != 0 GOTO ERROR_HANDLER

				--does record already exist in FileAdmin?
				IF Exists (Select 1 from dbo.Fileadmin Where txtCompanyNum = @compNum And blnExported = 0 And txtFileType = 'PDFSTMT' And blnAutomatedRequest = 1)
					BEGIN
					IF @freq = 'CDPLUS'
						BEGIN
							--if today's date is less than or equal to the cycle date + duedate, update record in FileAdmin table with the rundate as cycledate+duedate
							IF (SELECT DATEDIFF(d,GETDATE(),(@cd + CAST(@duedate as int)))) >= 0
								BEGIN
								Update dbo.FileAdmin
								Set dteRunDate = @rundate
								Where txtCompanyNum = @compNum And blnExported = 0 And txtFileType = 'PDFSTMT' And blnAutomatedRequest = 1

								IF @@ERROR != 0 GOTO ERROR_HANDLER
								END
						END
					ELSE	--DOM
						BEGIN
						Delete From dbo.FileAdmin
						Where txtCompanyNum = @compNum And blnExported = 0 And txtFileType = 'PDFSTMT' And blnAutomatedRequest = 1

						IF @@ERROR != 0 GOTO ERROR_HANDLER
						END
					END

				COMMIT TRANSACTION

				END
			ELSE		--due date is empty
				BEGIN
				
				BEGIN TRANSACTION

				DELETE FROM dbo.AutoRun
				WHERE CompNum = @compNum AND FileType = 'PDFSTMT'

				IF @@ERROR != 0 GOTO ERROR_HANDLER

				UPDATE dbo.CompNames
				Set AutomateStmts = 0
				Where CompNum = @compNum AND InternetNumber = @icn

				IF @@ERROR != 0 GOTO ERROR_HANDLER

				DELETE From dbo.FileAdmin
				Where txtCompanyNum = @compNum And blnExported = 0 And txtFileType = 'PDFSTMT' And blnAutomatedRequest = 1

				IF @@ERROR <> 0 GOTO ERROR_HANDLER
					
				COMMIT TRANSACTION

				END
			END
		END
	ELSE			--stmt not requested
		BEGIN
		IF @bookexists = 1
			BEGIN

			BEGIN TRANSACTION

			DELETE FROM dbo.AutoRun
			WHERE CompNum = @compNum AND FileType = 'PDFSTMT'

			IF @@ERROR != 0 GOTO ERROR_HANDLER

			UPDATE dbo.CompNames
			Set AutomateStmts = 0
			Where CompNum = @compNum AND InternetNumber = @icn

			IF @@ERROR != 0 GOTO ERROR_HANDLER

			DELETE From dbo.FileAdmin
			Where txtCompanyNum = @compNum And blnExported = 0 And txtFileType = 'PDFSTMT' And blnAutomatedRequest = 1

			IF @@ERROR != 0 GOTO ERROR_HANDLER
				
			COMMIT TRANSACTION
			
			END
		END

	--remove member element from the array
	select @compNums = stuff(@compNums, 1, @separator_comp, '')
	select @duedates = stuff(@duedates, 1, @separator_date, '')
	select @frequencies = stuff(@frequencies, 1, @separator_freq, '')
	select @cycledates = stuff(@cycledates, 1, @separator_cycle, '')
	
END


ERROR_HANDLER:
	ROLLBACK TRANSACTION
        SET NOCOUNT OFF
	
SET NOCOUNT OFF

GO

BEGIN TRAN

...code

	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @Error_Out <> 0
		BEGIN
			Select @Error_Loc = 51
			Select @Error_Type = 50001
  			GOTO Load_Ledger_Init_sp_Error
		END

	If @Result_Count = 0
		BEGIN
			SELECT @Error_Loc = 52
			SELECT @Error_Message = 'No Rows Inserted in to the BATCH_CNTL Table For Third Months File'
			SELECT @Error_Type = 50002
  			GOTO Load_Ledger_Init_sp_Error
		END


COMMIT TRAN

Load_Ledger_Init_sp_Exit:

SET NOCOUNT OFF

return @Comp_Code

Load_Ledger_Init_sp_Error:
ROLLBACK TRAN

Select @Comp_Code = -1, @Comp_msg = @Error_Message, @Load_Ind = 'N'

-- Values set during error handling in the code

If @Error_Type = 50001
	BEGIN
		Select @error_message = (Select 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
					     + '  @@ERROR: ' + RTRIM(Convert(char(6),error))
					     + ' Severity: ' + RTRIM(Convert(char(3),severity))
					     + '  Message: ' + RTRIM(description)
				  	   From master..sysmessages
				 	  Where error = @error_out)
	END
If @Error_Type = 50002
	BEGIN
		Select @Error_Message = 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
			              + ' Severity:  UserLevel '
				      + ' Message: ' + RTRIM(@Error_Message)
	END

RAISERROR @Error_Type @Error_Message

GOTO Load_Ledger_Init_sp_Exit
GO