mlcktmguy
asked on
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_Fr omYear_Thr uYear_Bala nce
EventID int
TaxAuthorityID int
FromYear int
ThruYear int
TABalance float
vtblHolds_PropID_TAID_Hold TypeID Thisis the view used by SPROC "spCheckExistenceOfHoldTyp e"
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.
This is the SPROC executed by the above SPROC
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_Fr
EventID int
TaxAuthorityID int
FromYear int
ThruYear int
TABalance float
vtblHolds_PropID_TAID_Hold
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER