Problem with SPROC creating two temp tables but not returning combined recordset
I am fairly new to SQL Server, using Developer 14. This is my first attempt at using temp tables.
I've created a SPROC that seems to be working as I hoped, except for the fact that no records are returned from the join of the two temp tables.
Generally the SPROC has 3 steps.
1. Using the passed parameters execute SPROC '[spGetMatchingAddresses]'
2. Using the passed parameters execute SPROC '[spGetCandidatesForLBL]' with the results populating temp table #tempTaxYears
3. Return a recordset joining the records from #tempTaxYears and #tempAddr based on the propertyID field. In this join there by zero to many matching addresses for each tax year record. So I make this a left join because I want all of the records regardless of whether there is a matching address.
When I execute the SPROC in SQL Server Management Studio by right clicking the SPROC and selecting 'Execute' it shows both temp tables and the records selected for each based on the passed filtering criterion. Everything looks as expected. Theproblem is that there are no final records showing as the result of my select
I don't understand why there are no records output from the final select statement but then again I've never used temp tables before.
Can anyone advise why there a no records in the final result set and also how I can correct this issue?
Here is the SPROC I am referring to:
This is the address selection SPROC that is executed from the main SPROC
This is the tax year filtering SPROC that is executed from the main SPROC
I've created a SPROC that seems to be working as I hoped, except for the fact that no records are returned from the join of the two temp tables.
Generally the SPROC has 3 steps.
1. Using the passed parameters execute SPROC '[spGetMatchingAddresses]'
2. Using the passed parameters execute SPROC '[spGetCandidatesForLBL]' with the results populating temp table #tempTaxYears
3. Return a recordset joining the records from #tempTaxYears and #tempAddr based on the propertyID field. In this join there by zero to many matching addresses for each tax year record. So I make this a left join because I want all of the records regardless of whether there is a matching address.
When I execute the SPROC in SQL Server Management Studio by right clicking the SPROC and selecting 'Execute' it shows both temp tables and the records selected for each based on the passed filtering criterion. Everything looks as expected. Theproblem is that there are no final records showing as the result of my select
Select * from #tempTaxYears
Left Join #tempAddr
on #tempTaxYears.PropertyID = #tempAddr.PropertyRecID I don't understand why there are no records output from the final select statement but then again I've never used temp tables before.
Can anyone advise why there a no records in the final result set and also how I can correct this issue?
Here is the SPROC I am referring to:
USE [JTSConversion]
GO
/****** Object: StoredProcedure [dbo].[spGetCandidatesForLBLwithAddresses] Script Date: 11/13/2017 11:20:00 AM ******/
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)
ENDThis is the address selection SPROC that is executed from the main SPROC
USE [JTSConversion]
GO
/****** Object: StoredProcedure [dbo].[spGetMatchingAddresses] Script Date: 11/13/2017 11:31:44 AM ******/
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 This is the tax year filtering SPROC that is executed from the main SPROC
USE [JTSConversion]
GO
/****** Object: StoredProcedure [dbo].[spGetCandidatesForLBLwithAddresses] Script Date: 11/13/2017 11:32:30 AM ******/
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)
ENDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
Please. Glad I could help.
ASKER
The SPROCS being called from the SPROC defining the temp tables are very simple. They take the passed parameters and format a select statement to create a filtered recordset.
Based on your scope explanation I pulled the select statements from the executed SPROCS directly into the main SPROC. Each select populates the respective temp table. The final join then works perfectly since the temp tables are its scope.
Perfect.
This is the final SPROC:
Open in new window