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]' with the results populating temp table #tempAddr

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 

Open in new window


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)

END

Open in new window


This 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 

Open in new window


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)

END

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
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.

PortletPaulfreelancerCommented:
I'm afraid you have fallen victim to the "scope of temporary tables" problem.
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
see Create Table
0

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:
Thank you.  

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:
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetCandidatesForLBLwithAddresses_2]    Script Date: 11/13/2017 6:41:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	spGetLBLCandidates
-- =============================================
ALTER PROCEDURE [dbo].[spGetCandidatesForLBLwithAddresses_2] 
	-- 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#
--
	insert Into #tempAddr
	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)));

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

insert into #tempTaxYears
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)



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

END

Open in new window

0
mlcktmguyAuthor Commented:
Thank you
0
PortletPaulfreelancerCommented:
Please. Glad I could help.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.