--Declare Variables
Declare @CurrentProgramID int
Declare @CurrentClientID int
Declare @AccountsTilCompletion int
Declare @CurrentReturnName char(15)
Declare @CurrentReturnType int

--Initialize variables to begin looping through locates
Set @CurrentProgramID = (Select Min(ProgramID) From DailyLocates Where DailyLocates.WasProcessed = 0)
Set @CurrentClientID = (Select Min(ClientID) From DailyLocates Where DailyLocates.ProgramID = @CurrentProgramID)
Set @AccountsTilCompletion = (Select Count(*) From DailyLocates Where DailyLocates.WasProcessed = 0)

--Loop until all Located Accounts have been processed

While (@AccountsTilCompletion > 0)
Begin
		Set @CurrentProgramID = (Select Min(ProgramID) From DailyLocates Where DailyLocates.WasProcessed = 0);

		Set @CurrentReturnName = (Select distinct(ReturnName) From DailyLocates Where DailyLocates.ProgramID = @CurrentProgramID);

		Set @CurrentReturnType = (Select Min(ReturnTypeID) From DailyLocates Where DailyLocates.ProgramID = @CurrentProgramID);

		--Invoke DTS packages to run based on ReturnFile Type
		Begin Select @CurrentReturnType
		If @CurrentReturnType = 0 GoTo CatchTheException;
		If @CurrentReturnType = 1 GoTo CreateXLSReturn;
		If @CurrentReturnType =  2  GoTo CreateTextReturn;
		If @CurrentReturnType =  3 GoTo CreatePDFReturn;
		If @CurrentReturnType = 4  GoTo CreateCSVReturn;
		If @CurrentReturnType =  5  GoTo CreateFaxReturn;
		If @CurrentReturnType =  6  GoTo CreateXLSPDFReturn;
		End
			--Loop until DTS package has completed
			--When DTS package completes, DailyLocates.WasProcessed will be set to '1' on all current accounts and this loop will end
			While((Select Count(*) From DailyLocates Where DailyLocates.WasProcessed = 0 AND DailyLocates.ProgramID = @CurrentProgramID) > 0)
			Begin
				WaitFor Delay '00:00:02';
			End

		Set @CurrentProgramID = (Select Min(ProgramID) From DailyLocates Where DailyLocates.WasProcessed = 0);
		Set @AccountsTilCompletion = (Select Count(*) From DailyLocates Where DailyLocates.WasProcessed = 0);
End

CatchTheException:
	Update DailyLocates
	Set WasProcessed = 2
	Where DailyLocates.ProgramID = @CurrentProgramID
Return

CreateXLSReturn:
	Update DailyLocates
	Set WasProcessed = 3
	Where DailyLocates.ProgramID = @CurrentProgramID
Return

CreateTextReturn:
	Update DailyLocates
	Set WasProcessed = 4
	Where DailyLocates.ProgramID = @CurrentProgramID
Return

CreatePDFReturn:
	Update DailyLocates
	Set WasProcessed = 5
	Where DailyLocates.ProgramID = @CurrentProgramID
Return

CreateCSVReturn:
	Update DailyLocates
	Set WasProcessed = 6
	Where DailyLocates.ProgramID = @CurrentProgramID
Return

CreateFaxReturn:
	
	Update DailyLocates
	SET WasProcessed = 7
	Where DailyLocates.ProgramID = @CurrentProgramID

Return

CreateXLSPDFReturn:
	Update DailyLocates
	Set WasProcessed = 8
	Where DailyLocates.ProgramID = @CurrentProgramID
Return
GO

--Invoke DTS packages to run based on ReturnFile Type
		Begin Select @CurrentReturnType
		If @CurrentReturnType = 0 GoTo CatchTheException;
		If @CurrentReturnType = 1 GoTo CreateXLSReturn;
		If @CurrentReturnType =  2  GoTo CreateTextReturn;
		If @CurrentReturnType =  3 GoTo CreatePDFReturn;
		If @CurrentReturnType = 4  GoTo CreateCSVReturn;
		If @CurrentReturnType =  5  GoTo CreateFaxReturn;
		If @CurrentReturnType =  6  GoTo CreateXLSPDFReturn;
		End
Print @CurrentReturnType
Print @AccountsTilCompletion

quote:

---

Originally posted by ahouse

I added the print statements periodically and found that my procedure never gets past the function call.

Is it legal to use nested Begin and End statements? Are my functions missing a piece allowing them to return to the while loop to finish executing?

--Invoke DTS packages to run based on ReturnFile Type
		Begin Select @CurrentReturnType
		If @CurrentReturnType = 0 GoTo CatchTheException;
		If @CurrentReturnType = 1 GoTo CreateXLSReturn;
		If @CurrentReturnType =  2  GoTo CreateTextReturn;
		If @CurrentReturnType =  3 GoTo CreatePDFReturn;
		If @CurrentReturnType = 4  GoTo CreateCSVReturn;
		If @CurrentReturnType =  5  GoTo CreateFaxReturn;
		If @CurrentReturnType =  6  GoTo CreateXLSPDFReturn;
		End
Print @CurrentReturnType
Print @AccountsTilCompletion

---