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
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

Open in new window

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 

Open in new window


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)

Open in new window



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

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
In this case you have to do like below-
1. Create a temp table
2. Execute sp and get data into the temp table, for this sample given.
2. Join the temp table and the view to get the required data.

E.g.
CREATE TABLE #temp(PropertyRecID,InterestPartyTypeID, Muni)
EXEC spGetMatchingAddresses 'value1', 'value2','value3'

SELECT * FROM #temp	t
OUTER APPLY 
(
	SELECT * FROM YourViewName v
	ON v.propertyId = t.propertyId
)t

Open in new window


or you can do with join /*Join  you can decide based on you need - I think left join will work here*/

CREATE TABLE #temp(PropertyRecID,InterestPartyTypeID, Muni)
EXEC spGetMatchingAddresses 'value1', 'value2','value3'

SELECT * FROM #temp	t
FULL JOIN YourViewName v /*or left join*/
ON v.propertyId = t.propertyId

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks you but I can't quit get the syntax correct.

Here's my revised Proc:
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetCandidatesForLBLwithAddresses]    Script Date: 11/12/2017 7:29:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	spGetLBLCandidates
-- =============================================
ALTER PROCEDURE [dbo].[spGetCandidatesForLBLwithAddresses] 
	-- Add the parameters for the stored procedure here
	--
	--  All parameters are required
	--
	--  These are the tax year parameters
	--
	@passedMuniCode int = 0,
	@passedTaxTypeID int = 0, 
	@passedTaxYear int = 9999,
	@passedDollarCutoff  float,
	--
	-- For passing to Address build, none of these are required
	--
    @passedPropertyID     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 added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Create Table #temp(PropertyRecID       int,
                   InterestPartyTypeID int, 
				   Muni                int, 
				   BillingAddressYN    bit, 
				   Active_YN           bit,
		           JTSAddr1            nvarchar(30), 
				   JTSAddr2            nvarchar(30), 
				   JTSAddr3            nvarchar(30), 
				   JTSAddr4            nvarchar(30),	
	               Name1               nvarchar(100), 
				   Name2               nvarchar(100), 
				   Name3               nvarchar(100), 
				   Address1            nvarchar(100), 
				   Address2            nvarchar(100), 
				   City                nvarchar(50), 
				   State               nvarchar(50), 
				   ZipCode             nvarchar(50), 
				   ZipPlusFour         nvarchar(50))
	
--
--  Pull addresses putting them into temp#
--
  Execute dbo.[spGetMatchingAddresses] @passedPropertyID,
								       @passedMuniCOde,
									   @passedOnlyActive_YN,
									   @passedIncludeInterestedPartyTypeID01,
									   @passedIncludeInterestedPartyTypeID02,
									   @passedIncludeInterestedPartyTypeID03,
									   @passedIncludeInterestedPartyTypeID04,
									   @passedIncludeInterestedPartyTypeID05,
									   @passedIncludeInterestedPartyTypeID06,
									   @passedIncludeInterestedPartyTypeID07,
									   @passedIncludeInterestedPartyTypeID08

 Select PropertyID, TaxAuthorityID, TaxTypeID, TaxYearRecID, TaxYear, Muni, LotBlock, TieBreaker, YearBalance 
                From vtblTaxYears_CandidatesForLBL vTaxYears
                Where (Muni       =  @passedMuniCode and 
                      TaxYear     = @passedTaxYear and
					  TaxTypeID   = @passedTaxTypeID and
					  YearBalance >= @passedDollarCutoff)
Left Join Temp# on vTaxyears.ProopertyID = temp#.propertyid
                

END

Open in new window


I don't have the syntax correct on the Select.  
"Msg 156, Level 15, State 1, Procedure spGetCandidatesForLBLwithAddresses, Line 85
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?
mlcktmguyAuthor Commented:
I revised the sproc again.  This time I create a temp table for addresses (#tempAddr)and a temp table for selected tax years (#tempTaxYears )

When I test the SPROC in SSMA I see that both temp tables are created.  However, there is no recordset created when I join the tables together using this statement

Select * from  #tempTaxYears
Left Join #tempAddr
on #tempTaxYears.PropertyID = #tempAddr.PropertyRecID 

Open in new window


With the join I am using, at the very least I should have all of the #tempTaxYears records, even if there is no matching address record but that isn't what's happening.  My result set is empty.

The general flow below is
Create #tempAddr
Execute the Sproc that loads #tempAddr

Create #tempTaxYears
Execute the SPROC that creates #tempTaxYears

Join the tables together to get the combined result set which would be, all records from tempTaxYears linked to any matching addresses.

Can anyone spot the issue?



USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetCandidatesForLBLwithAddresses]    Script Date: 11/12/2017 9:42:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	spGetLBLCandidates
-- =============================================
ALTER PROCEDURE [dbo].[spGetCandidatesForLBLwithAddresses] 
	-- Add the parameters for the stored procedure here
	--
	--  All parameters are required
	--
	@passedMuniCode int = 0,
	@passedTaxTypeID int = 0, 
	@passedTaxYear int = 9999,
	@passedDollarCutoff  float,
	--
	-- For passing to Address build
	--
    @passedPropertyID     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 added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Create Table #tempAddr(PropertyRecID       int,
					   InterestPartyTypeID int, 
					   Muni                int, 
					   BillingAddressYN    bit, 
					   Active_YN           bit,
					   JTSAddr1            nvarchar(30), 
					   JTSAddr2            nvarchar(30), 
					   JTSAddr3            nvarchar(30), 
					   JTSAddr4            nvarchar(30),	
					   Name1               nvarchar(100), 
					   Name2               nvarchar(100), 
					   Name3               nvarchar(100), 
					   Address1            nvarchar(100), 
					   Address2            nvarchar(100), 
					   City                nvarchar(50), 
					   State               nvarchar(50), 
					   ZipCode             nvarchar(50), 
					   ZipPlusFour         nvarchar(50))
	
--
--  Pull addresses putting them into temp#
--
  Execute dbo.[spGetMatchingAddresses] @passedPropertyID,
								       @passedMuniCOde,
									   @passedOnlyActive_YN,
									   @passedIncludeInterestedPartyTypeID01,
									   @passedIncludeInterestedPartyTypeID02,
									   @passedIncludeInterestedPartyTypeID03,
									   @passedIncludeInterestedPartyTypeID04,
									   @passedIncludeInterestedPartyTypeID05,
									   @passedIncludeInterestedPartyTypeID06,
									   @passedIncludeInterestedPartyTypeID07,
									   @passedIncludeInterestedPartyTypeID08


Create Table #tempTaxYears (PropertyID     int,
							TaxAuthorityID int, 
							TaxTypeID      int,
							TaxYearRecID   int, 
							TaxYear        int,
							Muni           int,
							LotBlock       nvarchar(17),
							TieBreaker     nvarchar(2),
							YearBalance    float)


Execute dbo.spGetCandidatesForLBL @passedMuniCode,
								    @passedTaxTypeID,
									@passedTaxYear,
									@passedDollarCutoff

Select * from  #tempTaxYears
Left Join #tempAddr
on #tempTaxYears.PropertyID = #tempAddr.PropertyRecID 


 --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

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.