Combining Two SProcs and Related Views To Return One Filtered Recordset
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
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 ONGOSET QUOTED_IDENTIFIER ONGOAlter 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
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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 floatASBEGIN -- 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
Here's my revised Proc:
Open in new window
I don't have the syntax correct on the Select.
"Msg 156, Level 15, State 1, Procedure spGetCandidatesForLBLwithA
Incorrect syntax near the keyword 'Left'."
I know I want to join the tables but I have to filter the tax year tables at the same time.
Any idea how that would look combined into one select?