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
Obvioulsy my SPROC is lacking correct syntax and correct execution but Hopefully it can be made to accomplish the objective.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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.