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

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

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

larryhSr. Software EngineerCommented:
To exit the SPROC early use the RETURN statement.  

If you want the SPROC to return a result to the calling procedure make sure you issue a SELECT statement that doesn't just assign a value to a variable.  For example,
SELECT @ReturnDidItQualify

For the code:
 If @RestrictedHoldFOund=1
		@ReturnDidItQualify='n'

Open in new window

Use
 If @RestrictedHoldFOund=1
		SET @ReturnDidItQualify='n'

Open in new window


You need to use SET or SELECT to assign a value to a variable.  There are subtle differences between these two commands so look in the BOL (Books OnLine) to learn more about this.
0
Scott PletcherSenior DBACommented:
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spQualification]

            @passedTaxAuthorityID            int,
            @passedMatchingEvent            int,
            @passedMatchingTaxYear            int,
            @passedExclusionHOldType    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            float = 100,
            @ReturnDidItQualify              char(1) = 'N'  Output

 AS
 SET NOCOUNT ON;

 Declare @RestrictedHoldFOund int
 Declare @passedPropertyID    int

 SET @RestrictedHoldFOund = 0
 SET @passedPropertyID = 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 dbo.spCheckExistenceOfHoldType @passedPropertyID, @passedTaxAuthorityID, @passedExclusionHOldType, @RestrictedHoldFOund

 If @RestrictedHoldFOund=1
 Begin
            Set @ReturnDidItQualify='N'
        Return 0
 End /*If*/
--
--  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.
--
--
If Not 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)
)
Begin
    Set @ReturnDidItQualify = 'N'
    Return
End /*If*/

--
-- 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
               ((EventID IN (@passedExclusionEvent01, @passedExclusionEvent02, @passedExclusionEvent03, @passedExclusionEvent04,
                             @passedExclusionEvent05, @passedExclusionEvent06, @passedExclusionEvent07,@passedExclusionEvent08)))
         )
                     Then 'N'
                     Else 'Y'
                     End
--
--  If none of the exclusions match then record qualifies
--
 RETURN
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thank you both very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.