asked on

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mlcktmguy

ASKER

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?

mlcktmguy

ASKER

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