Coding a multi-functional Record filtering SPROC

I am pretty new to SQL Server and have encountered a situation that is way beyond my SPROC coding ability.

I have done some straightforward SPROCS but nothing this involved.  I took my best shot but it's not even good enough to compile cleanly.

Here are the views referenced;
vtblEvents_EventID_TAID_FromYear_ThruYear_Balance
EventID                  int
TaxAuthorityID     int
FromYear              int
ThruYear               int
TABalance             float


vtblHolds_PropID_TAID_HoldTypeID  Thisis the view used by SPROC  "spCheckExistenceOfHoldType"
PropertyID             int
TaxAuthorityID     int
HoldTypeID           int

The SPROC I tried to create should perform a sequence of qualifying and disqualifying steps to determine if the passed TaxAuthority record is qualified.

This is the SPROC, with all of its flaws,  as I created it.  I put many comments in the logic to explain what each step is trying to accomplish and what action to take based on the result of each step.  Hopefully I put in enough comments to explain the objective.  If not please let me know.

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spQualifications]    Script Date: 10/27/2017 4:45:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE [dbo].[spQualification] 

		@passedTaxAuthorityID		int,
		@passedMatchingEvent		int,
		@passedMatchingTaxYear		int,
		@passedExlusion HoldType	int,
		@passedExclusionEvent01		int	= null,
		@passedExclusionEvent02		int = null,
		@passedExclusionEvent03		int	= null,
		@passedExclusionEvent04		int	= null,
		@passedExclusionEvent05		int	= null,
		@passedExclusionEvent06		int	= null,
		@passedExclusionEvent07		int	= null,
		@passedExclusionEvent08		int	= null,
		@passedBalanceCutoff		double = 100,
		@ReturnDidItQualify  		vchar(1) = 'N'  Output

 AS
 SET NOCOUNT ON;

 Declare @RestrictedHoldFOund int 0
 Declare @passedPropertyID    int 0

 --
 -- This first execute calls a SPROC to dermine if the exclusion Hold is associated with this @passedTaxAuthorityID
 -- If it is there really is no need to check further and I can set the return code and exit the SPORC without
 -- going any further.
 --
 -- I don't know how to exit the SPROC without going any further.
 --
 -- In this case the Property passed to the SPROC is always 0, that's whiy I declared the variable @passedPropertyID

 Execute spCheckExistenceOfHoldType @passedPropertyID, @passedTaxAuthorityID, @passedExclusionHOldType, @RestrictedHoldFOund

 If @RestrictedHoldFOund=1
		@ReturnDidItQualify='n'
exit the SPROC ?????  -- How is this accomplished without falling all the way thru the SPROC
--
--  The next part of the logic is supposed to make sure the passed @passedMatchingEvent occurred for this
--  @passedTaxAuthorityID in the @passedMatchingTaxYear and that the balance of the record is
--  greater than or equal to the @passedBalanceCutoff
--
--  If thecriterion is not met there is no point in chekcing further we can exit the SPROC although
--  I odn't know hjow to do that.
--
--
 Select @ReturnDidItQualify = 
     Case When Exists( 
         Select EventID 
         From dbo.vtblEvents_EventID_TAID_FromYear_ThruYear_Balance
         Where (TaxAuthorityID = @passedTaxAuthorityID) and
		       (EventID        = @passedMatchingEvent) and
			   (@passedMatchingTaxYear Between FromYear and ThruYear) and
			   (TABalance     >= @passedBalanceCutoff)
			   Then 'Y' 
			   Else 'N' 
			   End

If @ReturnDidItQualify = 'n'
exit the SPROC ?????  -- How is this accomplished without falling all the way thru the SPROC
--
-- If we get to this point we know that there is no hold restriction on the @passedTaxAuthorityID
-- and there was an event for this matching event @passedTaxAuthorityID in the @passedMatchingTaxYear
-- greater than or equal to the @passedBalanceCutoff
--
-- Now we have to make sure there isn't an event matching any of the passedExcusionEvents  for this
-- @passedTaxAuthorityID in the year @passedMatchingTaxYear.
--
-- ALl of the @passedExclusionEvents are optional.  That's why I formatted the Select that way.
--
 Select @ReturnDidItQualify = 
    Case When Exists( 
         Select EventID 
         From dbo.vtblEvents_EventID_TAID_FromYear_ThruYear_Balance
         Where (TaxAuthorityID = @passedTaxAuthorityID) and
		       (@passedMatchingTaxYear Between FromYear and ThruYear) and
               ((@passedExclusionEvent01 = null  Or EventID        = @passedExclusionEvent01) or
                (@passedExclusionEvent02 = null  Or EventID        = @passedExclusionEvent02) or
				(@passedExclusionEvent03 = null  Or EventID        = @passedExclusionEvent03) or
				(@passedExclusionEvent04 = null  Or EventID        = @passedExclusionEvent04) or
				(@passedExclusionEvent05 = null  Or EventID        = @passedExclusionEvent05) or
				(@passedExclusionEvent06 = null  Or EventID        = @passedExclusionEvent06) or
				(@passedExclusionEvent07 = null  Or EventID        = @passedExclusionEvent07) or
				(@passedExclusionEvent08 = null  Or EventID        = @passedExclusionEvent08))
			   Then 'N' 
			   Else 'Y' 
			   End
-- 
--  If none of the exclusions match then record qualifies
--
 RETURN

Select all Open in new window

Obvioulsy my SPROC is lacking correct syntax and correct execution but Hopefully it can be made to accomplish the objective.

This is the SPROC executed by the above SPROC

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spCheckExistenceOfHoldType]    Script Date: 10/27/2017 7:00:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		MJO
-- Create date: 
-- Description:	Check For Holds
-- =============================================
ALTER PROCEDURE [dbo].[spCheckExistenceOfHoldType] 
       @passedPropertyID         int = 0, 
       @passedTaxAuthorityID     int = 0,
	   @passedHoldGroupID        int = 0,
       @returnHoldFound          int = 0 OUTPUT
 AS
 SET NOCOUNT ON;
 Select @returnHoldFound = 
     Case When Exists( 
         Select HoldTypeID 
         From dbo.vtblHolds_PropID_TAID_HoldTypeID
         Where (@passedPropertyID     = 0  Or PropertyID = @passedPropertyID) and
               (@passedTaxAuthorityID = 0  Or TaxAuthorityID = @passedTaxAuthorityID) and
                ((@passedHoldGroupID = 1 and (HoldTypeID In (Select HoldtypeID From dbo.vtblASupport_Holds_EnforcementHolds))) or
                 (@passedHoldGroupID = 2 and (HoldTypeID In (Select HoldtypeID From dbo.vtblASupport_Holds_NoticeHolds))) or 
                 (@passedHoldGroupID = 3 and (HoldTypeID In (Select HoldtypeID From dbo.vtblASupport_Holds_PaymentAlerts))) or 
                 (@passedHoldGroupID = 4 and (HoldTypeID In (Select HoldtypeID From dbo.vtblASupport_Holds_PayPlanAlerts)))))
			   Then 1 
			   Else 0 
			   End
 RETURN

Select all Open in new window