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
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
ASKER