Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Combining Two SProcs and Related Views To Return One Filtered Recordset

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft AccessMicrosoft SQL ServerSQL
3 Comments1 Solution133 ViewsLast Modified:
I am fairly new to SQL Server, using Developer 14 as the backend to an Access 2013 Front End

I have created two views and two sprocs that each return a recordset.  Currently I am combining the returned recordsets in the Access front end.  It works but I'm sure this could be more efficient if I combined them on the Server side and brought them back together.

The link between the recordset returned from spGetMatchingAddresses (AddressRecords) and spGetCandidatesForLBL(TaxRecords) is propertyID.

For each record returned from spGetCandidatesForLBL there will be zero to many address records.  I want all of the tax records returned regardless of whether they have a matching address record.

I envision one SPROC that would do all the filtering in both SPROCS and deliver a recordset of information combining the tax record information with the address record (If any) information by PropertyID onto one returned record.  All tax records should be returned.

I just don’t know how to accomplish this with my limited knowledge of SQL

How could this be accomplished?

Here are the players:
View for address info:  vtblPropertyAddress_PropID_Type_Active_AndAddrInfo
SELECT        PropAddressID, PropertyRecID, InterestTypeID AS InterestPartyTypeID, Muni, BillingAddressYN, Active_YN, JTSAddr1, JTSAddr2, JTSAddr3, JTSAddr4, 
                         JTSSeqNumber, Name1, Name2, Name3, Address1, Address2, City, State, ZipCode, ZipPlusFour
FROM            dbo.tblProperty_Addresses
SPROC to Filter and pull address records.  Parameters are passed to this SPROC and it returns a recordset of all qualifying address records.
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[[spGetMatchingAddresses]]    Script Date: 11/11/2017 1:29:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[spGetMatchingAddresses] 

             @passedPropertyID     int = null,
			 @passedMuniCOde       int = null,
             @passedOnlyActive_YN  bit = true,

             @passedIncludeInterestedPartyTypeID01  int = null,
			 @passedIncludeInterestedPartyTypeID02  int = null,
			 @passedIncludeInterestedPartyTypeID03  int = null,
             @passedIncludeInterestedPartyTypeID04  int = null,
			 @passedIncludeInterestedPartyTypeID05  int = null,
			 @passedIncludeInterestedPartyTypeID06  int = null,
             @passedIncludeInterestedPartyTypeID07  int = null,
			 @passedIncludeInterestedPartyTypeID08  int = null

  AS
  Begin

  SET NOCOUNT ON;

	SELECT PropertyRecID,InterestPartyTypeID, Muni, BillingAddressYN, Active_YN,
		   JTSAddr1, JTSAddr2, JTSAddr3, JTSAddr4,	
	       Name1, Name2, Name3, Address1, Address2, City, State, ZipCode, ZipPlusFour
	From vtblPropertyAddress_PropID_Type_Active_AndAddrInfo
	WHERE 
          ((@passedPropertyID                     is null) or (PropertyRecID       = @passedPropertyID))	 AND
		  ((@passedMuniCOde                       is null) or (Muni                = @passedMuniCOde))       AND
		  ((@passedOnlyActive_YN                  is null) or (Active_YN           = @passedOnlyActive_YN))  AND
		  ((@passedIncludeInterestedPartyTypeID01 is null) or (InterestPartyTypeID IN (@passedIncludeInterestedPartyTypeID01,
																		  			   @passedIncludeInterestedPartyTypeID02,
																					   @passedIncludeInterestedPartyTypeID03,
																					   @passedIncludeInterestedPartyTypeID04,
																					   @passedIncludeInterestedPartyTypeID05,
																					   @passedIncludeInterestedPartyTypeID06,
																					   @passedIncludeInterestedPartyTypeID07,
																					   @passedIncludeInterestedPartyTypeID08,
																					   @passedIncludeInterestedPartyTypeID01)));

 --
end 
  RETURN 

View to pull tax records  'vtblTaxYears_CandidatesForLBL'
SELECT        dbo.tblTaxAuthority.TaxAuthorityID, dbo.tblTaxAuthority.TaxTypeID, dbo.tblTaxYears.TaxYearRecID, dbo.tblTaxYears.TaxYear, 
                         dbo.vADrop_Property_Muni_LB_TB.Muni, dbo.vADrop_Property_Muni_LB_TB.LotBlock, dbo.vADrop_Property_Muni_LB_TB.TieBreaker, 
                         ROUND(dbo.tblTaxYears.FaceBal + dbo.tblTaxYears.PenaltyBal + dbo.tblTaxYears.InterestBal + dbo.tblTaxYears.LienCostBal + dbo.tblTaxYears.SvcChgBal, 2) 
                         AS YearBalance, dbo.tblTaxAuthority.PropertyID
FROM            dbo.tblTaxAuthority INNER JOIN
                         dbo.vADrop_Property_Muni_LB_TB ON dbo.tblTaxAuthority.PropertyID = dbo.vADrop_Property_Muni_LB_TB.PropertyRecID INNER JOIN
                         dbo.tblTaxYears ON dbo.tblTaxAuthority.TaxAuthorityID = dbo.tblTaxYears.TaxAuthorityRecID
WHERE        (dbo.tblTaxYears.NBLDate IS NULL) AND (dbo.tblTaxYears.LienDTDNumber IS NULL OR
                         dbo.tblTaxYears.LienDTDNumber = 0) AND (dbo.tblTaxYears.LienAsgnDTDNumber IS NULL OR
                         dbo.tblTaxYears.LienAsgnDTDNumber = 0) AND (dbo.tblTaxYears.PayStatusID <> 1) AND (dbo.tblTaxYears.Full_Int_Add_TypeID = 1) AND 
                         (ROUND(dbo.tblTaxYears.FaceBal + dbo.tblTaxYears.PenaltyBal + dbo.tblTaxYears.InterestBal + dbo.tblTaxYears.LienCostBal + dbo.tblTaxYears.SvcChgBal, 2) > 0)


SPROC to pull tax records 'spGetCandidatesForLBL'.  Parameters are passed and a recordset of all qualifying records is returned.
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetCandidatesForLBL]    Script Date: 11/11/2017 4:58:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	spGetLBLCandidates
-- =============================================
ALTER PROCEDURE [dbo].[spGetCandidatesForLBL] 
	-- Add the parameters for the stored procedure here
	--
	--  All parameters are required
	--
	@passedMuniCode int = 0,
	@passedTaxTypeID int = 0, 
	@passedTaxYear int = 9999,
	@passedDollarCutoff  float
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
---	
 Select PropertyID, TaxAuthorityID, TaxTypeID, TaxYearRecID, TaxYear, Muni, LotBlock, TieBreaker, YearBalance 
                From vtblTaxYears_CandidatesForLBL 
                Where (Muni =  @passedMuniCode and 
                      TaxYear = @passedTaxYear and
					  TaxTypeID = @passedTaxTypeID and
					  YearBalance >= @passedDollarCutoff)

END