Link to home
Start Free TrialLog in
Avatar of Karl_mark
Karl_mark

asked on

SQL 2008 - Disappearing Temp Table in Stored Procedure

I've come across a strange issue which I haven't seen before and cannot figure out what is going on.
The stored procedure will be used in an SSRS Report and contains a fair number of temporary tables which are populated from multi-value parameters passed from SSRS. The code within the procedure parses the parameters and inserts them as individual rows in temporary tables which are later joined into the final query. So far, so good and this works fine in all the procs I have so far written.

However, I'm re-writing a procedure to add some new functionality. No new temp tables have been added, I'm simply altering some contents so data can be presented in a different way. One of the temp tables is called #DateRange, and is created thus:

IF OBJECT_ID('tempdb..#DateRange') IS NOT NULL DROP TABLE #DateRange
CREATE TABLE #DateRange
(
	ParDate DATE,
	YYYYMM VARCHAR(6),
	MonthYear VARCHAR(10),
	YearQuarter VARCHAR(6)
)

Open in new window


And is populated like this:
INSERT INTO #DateRange
SELECT
	DateRange.Date,
	DateRange.YYYYMM,
	DateRange.MonthYear,
	DateRange.YearQuarter
FROM
(
	SELECT DISTINCT
		dD.Date,
		dD.YYYYMM,
		dD.MonthYear,
		CAST(dD.Year AS VARCHAR(4)) + '-' + CAST(dD.Quarter AS Varchar(1)) AS YearQuarter
	FROM
		DimDate dD
	WHERE
		DATEPART(DAY, dD.Date) = 1
		AND dD.Date BETWEEN @FirstDate AND @LastDate
) DateRange

Open in new window


If I test the proc, then I can successfully select records from the table with no errors. However, if I run the whole procedure, it fails with the message 'Invalid Object name #DateRange'. I've dug down a bit deeper; the code immediately following the creation of the #DateRange table is:

IF OBJECT_ID('tempdb..#PITHeadcount') IS NOT NULL DROP TABLE #PITHeadcount
CREATE TABLE #PITHeadcount
(
	YYYYMM INT,
	MonthYear VARCHAR(10),
	Brand VARCHAR(100),
	AgeBandID INT,
	AgeBand NVARCHAR(100),
	Gender VARCHAR(10),
	SkaleID INT,
	Skale NVARCHAR(100),
	TenureID INT,
	Tenure NVARCHAR(100),
	HoldingCompany NVARCHAR(100),
	WorkCountry NVARCHAR(100),
	Employees INT
)

INSERT INTO #PITHeadcount
EXEC reports.REP_PITFTEHeadcount @HoldingCompany, @Brand, @WorkCountry, @AgeBand, @Gender, @Skale, @Tenure, @FirstDate, @LastDate

Open in new window


Now, if I add
SELECT * FROM #DateRange

Open in new window

prior to the EXEC command, then I get the results from the table. However, the line of code immediately following the EXEC command is:

ALTER TABLE #PITHeadcount
ADD YearQuarter VARCHAR(6)

UPDATE #PITHeadcount
	SET YearQuarter = dD.YearQuarter
FROM
	#PITHeadcount PH
	JOIN #DateRange dD ON PH.YYYYMM = dD.YYYYMM

Open in new window


The YearQuarter column is added to the #PITHeadcount table after the EXEC command because the Proc it calls does not return that column and of course, you cannot specify columns when populating a table from a stored proc. When it gets to the UPDATE statement, I get the 'Invalid Object' message. In fact, on further investigation, it seems that all my temp tables other than the #PITHeadcount table are lost after I use the EXEC command to populate the temp table.

Is this a known issue, or is there something I need to do to resolve this? It seems strange, because in other SPs I also use the EXEC command to populate a temp table and this does not affect existing tables.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Do you have any GO command there?
If so, this will make you loose any temporary table created before.
Could be a scope issue using your guard clause. E.g.

CREATE PROCEDURE dbo.p_InnerScope
AS
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#Test') IS NOT NULL
        DROP TABLE #Test;

    CREATE TABLE #Test ( Payload NVARCHAR(255) );
    INSERT  INTO #Test
    VALUES  ( 'Inner Scope' );

    SELECT  *
    FROM    #Test T;
GO

CREATE PROCEDURE dbo.p_OuterScope
AS
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#Test') IS NOT NULL
        DROP TABLE #Test;

    CREATE TABLE #Test ( Payload NVARCHAR(255) );
    INSERT  INTO #Test
    VALUES  ( 'Outer Scope' );

    SELECT  *
    FROM    #Test T;

    EXECUTE dbo.p_InnerScope;

    SELECT  *
    FROM    #Test T;
GO

EXECUTE dbo.p_OuterScope;
GO

Open in new window


vs.

CREATE PROCEDURE dbo.p_InnerScope
AS
    SET NOCOUNT ON;

    CREATE TABLE #Test ( Payload NVARCHAR(255) );
    INSERT  INTO #Test
    VALUES  ( 'Inner Scope' );

    SELECT  *
    FROM    #Test T;
GO

CREATE PROCEDURE dbo.p_OuterScope
AS
    SET NOCOUNT ON;

    CREATE TABLE #Test ( Payload NVARCHAR(255) );
    INSERT  INTO #Test
    VALUES  ( 'Outer Scope' );

    SELECT  *
    FROM    #Test T;

    EXECUTE dbo.p_InnerScope;

    SELECT  *
    FROM    #Test T;
GO

CREATE TABLE #Test ( Payload NVARCHAR(255) );
INSERT  INTO #Test
VALUES  ( 'No Scope' );

EXECUTE dbo.p_OuterScope;

SELECT  *
FROM    #Test T;

DROP TABLE #Test;
GO

Open in new window


As you can see in the second example, you don't need to clean-up your temporary tables, cause SQL Server does this when leaving the scope of their creation.
are you executing all this in a single session?
Avatar of Karl_mark
Karl_mark

ASKER

Thanks all.
First, I haven't used GO in the procedure as I know this causes it to lose the temp tables.
The entire code is issued in a single session.

As for the scope issue, I'm only using the test for the tables in the development stage; I know there is no need once it is converted to an SP.  HAving the test allows me to change table definition during development without having to manually drop tables. Having said that, I've created the SP and commented out the 'IF(OBJECT_ID...' test from the code and still get the same error when executing the stored procedure. I really cannot figure it out as I have plenty of other SPs that use much the same code!
And, FWIW, I just checked to see if perhaps the EXEC command was causing the issue, but it is not; another of the SPs uses this with no problems.
Let me insist once more on the GO keyword. You might have the GO included in the SP definition. If so, replace it with a RETURN.
Or in other words: Post a complete and concise example showing that behavior...

Just a comment: "..plenty of other SPs that use much the same code!" working code is not necessarily the same as correct code.
Victor:
I did a search on the text and the only places where 'GO' appears are prior to the ALTER (or CREATE) procedure statment:

USE [xxxxx]
GO
/****** Object:  StoredProcedure [reports].[REP_PITAttrition]    Script Date: 05/09/2017 10:31:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [reports].[REP_PITQuarterAttrition]

Open in new window

Ok, that shouldn't affect because is proper from the script option to add the USE clause.
Well, you might try with global temp table (two ## instead of #) to see if it helps:
CREATE TABLE ##DateRange
(...)

Open in new window

Whilst I appreciate your comment ste5an, I really don't think I can post the full example as you request. I don't want to get into a debate about 'working v correct code' as we all know that there are a multitude of different methods for reaching the same goal when it comes to programming. As far as it goes, here is a snapshot of code that does work (I do not want to clog up the thread with a massive SP):


ALTER PROCEDURE [reports].[REP_PITAttrition]
--DECLARE
	@Brand NVARCHAR(100) = NULL,
	@HoldingCompany NVARCHAR(100) = NULL,
	@WorkCountry NVARCHAR(100) = NULL,
	@AgeBand NVARCHAR(100) = NULL,
	@Gender NVARCHAR(100) = NULL,
	@Skale NVARCHAR(100) = NULL,
	@Tenure NVARCHAR(100) = NULL,
	@FirstDate DATE,
	@LastDate DATE,
	@Vol NVARCHAR(3) = NULL
AS


--SELECT
	--@Brand = 'ALL',
	--@Gender = 'ALL',
	--@Skale = 'ALL',
	--@Tenure = 'ALL',
	--@FirstDate = '2016-05-01',
	--@LastDate = '2016-08-31'

--Create a temp table to hold the brands that have been passed to the procedure
IF OBJECT_ID('tempdb..#BrandTable')	IS NOT NULL DROP TABLE #BrandTable
CREATE TABLE #BrandTable 
(
	NAME VARCHAR(100)
)
DELETE #BrandTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'x,x'
INSERT INTO #BrandTable
	(NAME)
SELECT 
	C.NAME
FROM 
	COMPANY C  
WHERE   
	CHARINDEX(',' +  C.NAME + ',',','+ REPLACE(@Brand, ', ',',')+',') > 0  
	
UNION 
-- Second SELECT statement add all Brands from COMPANY table is 'ALL' has been passed as a parameter value
SELECT 
	NAME   
FROM
	COMPANY C  
WHERE 
	@Brand = 'ALL'
	
UNION
--If no value has been passed for the Brand parameter, then 'Total' is added to Brand temp table. This will sum the value across all brands
SELECT
	'Total'
WHERE 
	@Brand is null

Open in new window



There then follows further definitions of temp tables for storing variables which utilise very similar code until we reach:

IF OBJECT_ID('tempdb..#PITHeadcount') IS NOT NULL DROP TABLE #PITHeadcount
CREATE TABLE #PITHeadcount
(
	YYYYMM INT,
	MonthYear VARCHAR(10),
	Brand VARCHAR(100),
	AgeBandID INT,
	AgeBand NVARCHAR(100),
	Gender VARCHAR(10),
	SkaleID INT,
	Skale NVARCHAR(100),
	TenureID INT,
	Tenure NVARCHAR(100),
	HoldingCompany NVARCHAR(100),
	WorkCountry NVARCHAR(100),
	Employees INT
)

INSERT INTO #PITHeadcount
EXEC reports.REP_PITFTEHeadcount @HoldingCompany, @Brand, @WorkCountry, @AgeBand, @Gender, @Skale, @Tenure, @FirstDate, @LastDate

Open in new window


Followed by some dynamic SQL generation which utilises the temp tables to return the dataset, which it does correctly (and promptly). Like mentioned in the first Post, if I add "SELECT * FROM temptable" prior to the EXEC command then I get results; if I move it to after then I get the "Invalid Object" error.
Is there a chance that you have another line of code that drops the #DateRange temp table?

Try this to search for another stored procedure that drops #DateRange

SELECT *
FROM sysobjects
WHERE id IN (
		SELECT id
		FROM syscomments
		WHERE TEXT LIKE '%#DateRange%' and text like '%drop%'
		)
ORDER BY NAME

Open in new window



Sample Code


I have been trying to reproduce the problem on my test machine and have not been able to so far.  I don't think the GO statement does anything to the temp table, at least not on my test machine.

Create #DateRange temp table and insert some sample data and query and use in another stored procedure
IF OBJECT_ID('tempdb..#DateRange') IS NOT NULL DROP TABLE #DateRange

-- create temp table
CREATE TABLE #DateRange
(
	ParDate DATE
)

-- Insert some test dates
INSERT INTO #DateRange
Select DateValue from
(
Select '2017-05-01' as DateValue
union
Select '2017-05-02' as DateValue
union 
Select '2017-05-03' as DateValue
) G1

GO
select * from #DateRange 

GO
exec sp_IsMyTempDateHere

Open in new window


Code of other Stored Procedure

CREATE   PROCEDURE [dbo].[sp_IsMyTempDateHere] 
AS

set nocount off

select * from #DateRange



GO

Open in new window

A concise and complete example is a single, short T-SQL script showing your codes behavior. Not different scripts. Based on system tables or Adventure Works or WorldWideImporters.

Either you crafting a sample of this kind, which is often already the key to understand your problem and thus to a solution or you post your entire procedure.

There is no other way. Cause you've already got all common reasons for failure. Anything else is wild guessing.
as per your code  -you should include temp table creation into the same "code" or use ##global  table  as Vitor advised
"Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server."
https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx
CREATE   PROCEDURE [dbo].[sp_IsMyTempDateHere] 
AS

set nocount off

select * from #DateRange

Open in new window

I think you are confusing my test setup code with the question authors code.

No, you don't want to use a ##global.  #DateRange works as long as the Stored procedure is called from the same session that created the table.
Jeff:   yes... wrong post

Agree with posts above-- something simple in the code
maybe typo-extra space in the table name

'Invalid Object name #DateRange'


Karl_mark:
 
can you please post -attach "all" proc that gives you error?
Ok, here goes:

USE [Reporting]
GO
/****** Object:  StoredProcedure [reports].[REP_PITAttrition]    Script Date: 05/09/2017 10:31:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [reports].[REP_PITQuarterAttrition]
--DECLARE
	@Brand NVARCHAR(100) = NULL,
	@HoldingCompany NVARCHAR(100) = NULL,
	@WorkCountry NVARCHAR(100) = NULL,
	@AgeBand NVARCHAR(100) = NULL,
	@Gender NVARCHAR(100) = NULL,
	@Skale NVARCHAR(100) = NULL,
	@Tenure NVARCHAR(100) = NULL,
	@FirstDate DATE = '2016-01-01',
	@LastDate DATE = '2016-12-31',
	@Vol NVARCHAR(3) = 'ALL'
AS


--Create a temp table to hold the brands that have been passed to the procedure
--IF OBJECT_ID('tempdb..#BrandTable')	IS NOT NULL DROP TABLE #BrandTable
CREATE TABLE #BrandTable 
(
	NAME VARCHAR(100)
)
DELETE #BrandTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'Kantar HQ,Kantar TNS'
INSERT INTO #BrandTable
	(NAME)
SELECT 
	C.NAME
FROM 
	COMPANY C  
WHERE   
	CHARINDEX(',' +  C.NAME + ',',','+ REPLACE(@Brand, ', ',',')+',') > 0  
	
UNION 
-- Second SELECT statement add all Brands from COMPANY table is 'ALL' has been passed as a parameter value
SELECT 
	NAME   
FROM
	COMPANY C  
WHERE 
	@Brand = 'ALL'
	
UNION
--If no value has been passed for the Brand parameter, then 'Total' is added to Brand temp table. This will sum the value across all brands
SELECT
	'Total'
WHERE 
	@Brand is null


--Create a temp table to hold the Holding Companies that have been passed to the procedure
--IF OBJECT_ID('tempdb..#HoldingcompanyTable')	IS NOT NULL DROP TABLE #HoldingCompanyTable
CREATE TABLE #HoldingCompanyTable 
(
	HoldingCompany VARCHAR(100)
)
DELETE #HoldingCompanyTable


--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'Kantar HQ,Kantar TNS'
INSERT INTO #HoldingCompanyTable
	(HoldingCompany)
SELECT 
	HC.NAME
FROM 
	HOLDINGCOMPANY HC  
WHERE   
	CHARINDEX(',' +  HC.NAME + ',',','+ REPLACE(@HoldingCompany, ', ',',')+',') > 0  
	
UNION 
-- Second SELECT statement add all Brands from COMPANY table is 'ALL' has been passed as a parameter value
SELECT 
	HC.NAME   
FROM
	HOLDINGCOMPANY HC  
WHERE 
	@HoldingCompany = 'ALL'
	
UNION
--If no value has been passed for the Brand parameter, then 'Total' is added to Brand temp table. This will sum the value across all brands
SELECT
	'Total'
WHERE 
	@HoldingCompany is null
	
	
--Create a temp table to hold the Countries that have been passed to the procedure
--IF OBJECT_ID('tempdb..#CountryTable')	IS NOT NULL DROP TABLE #CountryTable
CREATE TABLE #CountryTable 
(
	Country VARCHAR(100)
)
DELETE #CountryTable
--SELECT @Brand = ISNULL(@Brand, 'Total')
--SELECT @Brand

--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'Kantar HQ,Kantar TNS'
INSERT INTO #CountryTable
	(Country)
SELECT 
	OPV.DISPLAYVALUE
FROM
	COUNTRY CO
	JOIN OC_PICKLISTVALUES OPV ON CO.COUNTRY = OPV.STOREVALUE
	AND OPV.CULTURECODE = 'en-gb'
	AND OPV.PICKLISTID = 'F1DAC2DD-B4BC-4FA8-8E7D-8B5E5D7E00AE' 
WHERE   
	CHARINDEX(',' +  OPV.DISPLAYVALUE + ',',','+ REPLACE(@WorkCountry, ', ',',')+',') > 0  
	
UNION 
-- Second SELECT statement add all Brands from COMPANY table is 'ALL' has been passed as a parameter value
SELECT
	OPV.DISPLAYVALUE
FROM
	COUNTRY CO
	JOIN OC_PICKLISTVALUES OPV ON CO.COUNTRY = OPV.STOREVALUE
	AND OPV.CULTURECODE = 'en-gb'
	AND OPV.PICKLISTID = 'F1DAC2DD-B4BC-4FA8-8E7D-8B5E5D7E00AE'
WHERE
	CO.COUNTRY_ID IN
	(
		SELECT DISTINCT
			PO.COUNTRYNAME
		FROM
			POST PO
		WHERE
			CO.COUNTRY_ID = PO.COUNTRYNAME
			
	) 
AND @WorkCountry = 'ALL'
	
UNION
--If no value has been passed for the Brand parameter, then 'Total' is added to Brand temp table. This will sum the value across all brands
SELECT
	'Total'
WHERE 
	@WorkCountry is null
	
--Create a temp table to hold the Age Bands that have been passed to the procedure
--IF OBJECT_ID('tempdb..#AgeBandTable') IS NOT NULL DROP TABLE #AgeBandTable
CREATE TABLE #AgeBandTable
(
	AgeBandID INT,
	NAME NVARCHAR(100)
)

DELETE #AgeBandTable
--First SELECT statement parses the parameter if multiple values have been passed, i.e. '16-25,26-35'
INSERT INTO #AgeBandTable
	(AgeBandID, NAME)
SELECT
	dAB.AgeBandID,
	dAB.AgeBand
FROM
	dimAgeBands dAB
WHERE
	CHARINDEX(',' + dAB.AgeBand + ',',',' + REPLACE(@AgeBand, ', ',',') + ',') > 0
	
UNION
-- Second SELECT statement add all Age Bands from dimAgeBands table is 'ALL' has been passed as a parameter value
SELECT
	dAB.AgeBandID,
	dAB.AgeBand
FROM
	dimAgeBands dAB
WHERE
	@AgeBand = 'ALL'
	
UNION
--If no value has been passed for the AgeBand parameter, then 'Total' is added to AgeBand temp table. This will sum the value across all age bands
SELECT
	1,
	'Total'
WHERE
	@AgeBand IS NULL

--Create a temp table to hold the Genders that have been passed to the procedure
--IF OBJECT_ID('tempdb..#GenderTable') IS NOT NULL DROP TABLE #GenderTable
CREATE TABLE #GenderTable
(
	Gender VARCHAR(10)
)

DELETE #GenderTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'F,M'
INSERT INTO #GenderTable
(Gender)
SELECT
	DS.Item
FROM
	dbo.DelimitedSplit8K(@Gender, ',') DS
	
UNION 
-- Second SELECT statement adds both genders if 'ALL' has been passed as a parameter value
SELECT
	DS.Item
FROM
	dbo.DelimitedSplit8K('M,F,Unknown',',') DS
WHERE
	@Gender = 'ALL'
	
UNION
--If no value has been passed for the Gender parameter, then 'Total' is added to Gender temp table. This will sum the value across both genders
SELECT
	'Total'
WHERE
	@Gender IS NULL

--Create a temp table to hold the Skale Positions that have been passed to the procedure
--IF OBJECT_ID('tempdb..#SkaleTable') IS NOT NULL DROP TABLE #SkaleTable
CREATE TABLE #SkaleTable
(
	SkaleID INT,
	Skale NVARCHAR(100)
)

DELETE #SkaleTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. '80,90'
INSERT INTO #SkaleTable
(SkaleID, Skale)
SELECT
	dSO.SkaleGroupingOrder,
	dSO.SkaleGrouping
FROM
	reports.dimSkaleOrder dSO
WHERE
	CHARINDEX(',' + dSO.SkaleGrouping + ',',',' + REPLACE(@Skale, ', ',',') + ',') > 0
	
UNION
-- Second SELECT statement adds all skale positions from dimSkaleOrder table if 'ALL' has been passed as a parameter value
SELECT
	dSO.SkaleGroupingOrder,
	dSO.SkaleGrouping
FROM
	reports.dimSkaleOrder dSO
WHERE
	@Skale = 'ALL'

UNION
--If no value has been passed for the Skale parameter, then 'Total' is added to Skale temp table. This will sum the value across Skale Positions
SELECT
	1,
	'Total'
WHERE
	@Skale IS NULL

--IF OBJECT_ID('tempdb..#TenureTable') IS NOT NULL DROP TABLE #TenureTable
--Create a temp table to hold the Skale Positions that have been passed to the procedure
CREATE TABLE #TenureTable
(
	TenureID INT,
	Tenure NVARCHAR(100)
)

DELETE #TenureTable
--First SELECT statement parses the parameter if multiple values have been passed, i.e. '<2,2-5'
INSERT INTO #TenureTable
(TenureID, Tenure)
SELECT
	dT.dimTenure_ID,
	dT.TenureBand
FROM
	dimTenure dT
WHERE
	CHARINDEX(',' + dT.TenureBand + ',',',' + REPLACE(@Tenure, ', ',',') + ',') > 0
	
UNION
-- Second SELECT statement adds all Tenure bands from dimTenure table if 'ALL' has been passed as a parameter value
SELECT
	dT.dimTenure_ID,
	dT.TenureBand
FROM
	dimTenure dT
WHERE
	@Tenure = 'ALL'
	
UNION
--If no value has been passed for the Tenure parameter, then 'Total' is added to Skale temp table. This will sum the value across Tenure Bands
SELECT
	1,
	'Total'
WHERE
	@Tenure IS NULL
	

--Create a temp table to hold the voluntary/involuntary parameters passed to the procedure
--IF OBJECT_ID('tempdb..#VolTable') IS NOT NULL DROP TABLE #VolTable

CREATE TABLE #VolTable
(
	Vol NVARCHAR(5)
)

DELETE #VolTable
--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'V,I'
INSERT INTO #VolTable
(Vol)
SELECT
	DS.Item
FROM
	dbo.DelimitedSplit8K(@Vol, ',') DS
	
UNION 
-- Second SELECT statement adds values 'I' and 'V' to the temp table if 'ALL' has been passed as a parameter value
SELECT
	DS.Item
FROM
	dbo.DelimitedSplit8K('Inv,Vol',',') DS
WHERE
	@Vol = 'ALL'
	
UNION
--If no value has been passed for the Tenure parameter, then 'Total' is added to Voluntary temp table. 
--This will sum the value across both volunary and involuntary leavers
SELECT
	'Total'
WHERE
	@Vol IS NULL

--Create a temp table to hold the date range parameters passed to the procedure
--This is done so that we can convert to the first date of the month for the first date parameter,
--and the last date of the month for the last date parameter
--IF OBJECT_ID('tempdb..#DateRange') IS NOT NULL DROP TABLE #DateRange
CREATE TABLE #DateRange
(
	ParDate DATE,
	YYYYMM VARCHAR(6),
	MonthYear VARCHAR(10),
	YearQuarter VARCHAR(6)
)
--SELECT statement inserts a value for each month that falls between the first and last date of the parameters
--This allows us to return a value for the beginning of each month.


INSERT INTO #DateRange
SELECT
	DateRange.Date,
	DateRange.YYYYMM,
	DateRange.MonthYear,
	DateRange.YearQuarter
FROM
(
	SELECT DISTINCT
		dD.Date,
		dD.YYYYMM,
		dD.MonthYear,
		CAST(dD.Year AS VARCHAR(4)) + '-' + CAST(dD.Quarter AS Varchar(1)) AS YearQuarter
	FROM
		DimDate dD
	WHERE
		DATEPART(DAY, dD.Date) = 1
		AND dD.Date BETWEEN @FirstDate AND @LastDate
) DateRange

--SELECT * FROM #DateRange
/********************************************************************************************
Main code section.

First, we set up a temp table to hold the Headcount details for the date range and parameters
selected. This table is populated by calling the REP_PITHeadcount stored procedure and passes
the parameters that have been selected for the REP_PITAttrition procedure
********************************************************************************************/


--IF OBJECT_ID('tempdb..#PITHeadcount') IS NOT NULL DROP TABLE #PITHeadcount
CREATE TABLE #PITHeadcount
(
	YYYYMM INT,
	MonthYear VARCHAR(10),
	Brand VARCHAR(100),
	AgeBandID INT,
	AgeBand NVARCHAR(100),
	Gender VARCHAR(10),
	SkaleID INT,
	Skale NVARCHAR(100),
	TenureID INT,
	Tenure NVARCHAR(100),
	HoldingCompany NVARCHAR(100),
	WorkCountry NVARCHAR(100),
	Employees INT
)

--SELECT * FROM #DateRange
INSERT INTO #PITHeadcount
EXEC reports.REP_PITFTEHeadcount @HoldingCompany, @Brand, @WorkCountry, @AgeBand, @Gender, @Skale, @Tenure, @FirstDate, @LastDate

--SELECT * FROM #DateRange

ALTER TABLE #PITHeadcount
ADD YearQuarter VARCHAR(6)
--SELECT * FROM #DateRange
--Why doesn't #DateRange pick up? Invalid Object Name
UPDATE #PITHeadcount
	SET YearQuarter = dD.YearQuarter
FROM
	#PITHeadcount PH
	JOIN #DateRange dD ON PH.YYYYMM = dD.YYYYMM



/********************************************************************************************
Next, we set up a temp table to hold the Leaver details for the date range and parameters
selected. This table is populated by calling the REP_PITLeavers stored procedure and passes
the parameters that have been selected for the REP_PITAttrition procedure
********************************************************************************************/

--IF OBJECT_ID('tempdb..#PITLeavers') IS NOT NULL DROP TABLE #PITLeavers
CREATE TABLE #PITLeavers
(
	YYYYMM INT,
	MonthYear VARCHAR(10),
	Brand VARCHAR(100),
	HoldingCompany NVARCHAR(100),
	WorkCountry NVARCHAR(100),
	AgeBandID INT,
	AgeBand NVARCHAR(100),
	Gender VARCHAR(10),
	SkaleID INT,
	Skale NVARCHAR(100),
	TenureID INT,
	Tenure NVARCHAR(100),
	Voluntary NVARCHAR(5),
	Employees INT
)

INSERT INTO #PITLeavers
EXEC reports.REP_PITLeavers @HoldingCompany, @Brand, @WorkCountry, @AgeBand, @Gender, @Skale, @Tenure, @Vol, @FirstDate, @LastDate

ALTER TABLE #PITLeavers
ADD YearQuarter VARCHAR(6)

UPDATE #PITLeavers
	SET YearQuarter = dD.YearQuarter
FROM
	#PITLeavers PH
	JOIN #DateRange dD ON PH.YYYYMM = dD.YYYYMM

DECLARE @SQL NVARCHAR(MAX)

/***********************************************************************************
Start generating the SQL used in the final query.
***********************************************************************************/

DECLARE @SQLFrom NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL, '') + 'SELECT DISTINCT 
	'
	
/***********************************************************************************
Generate the SQL used in the FROM clause for the final query. This SQL is static
as the source tables will always be the same regardless of parameters selected
***********************************************************************************/
SELECT @SQLFrom = COALESCE(@SQLFrom, '') + 'FROM
	#PITHeadcount PH
	LEFT JOIN #PITLeavers PL ON PH.YearQuarter = PL.MonthYear
		AND PH.HoldingCompany = PL.HoldingCompany
		AND PH.Brand = PL.Brand
		AND PH.WorkCountry = PL.WorkCountry
		AND PH.AgeBandID = PL.AgeBandID
		AND PH.Gender = PL.Gender
		AND PH.SkaleID = PL.SkaleID
		AND PH.TenureID = PL.TenureID
'


/******************************************************************************************
Next generate the SQL for the fields to be returned. This section is dynamic because
if there is no value for a parameter then 'Total' will  be returned and a sum across all
values for the field will be returned
******************************************************************************************/

DECLARE @SQLFields NVARCHAR(MAX)
SELECT @SQLFields = COALESCE(@SQLFields, '') + 
	'PH.YearQuarter,
	' + CASE WHEN @Brand IS NOT NULL THEN 'PH.Brand' ELSE '''Total'''  END + ' AS Brand,'
	+ '
	' + CASE WHEN @HoldingCompany IS NOT NULL THEN 'PH.HoldingCompany' ELSE '''Total''' END + ' AS HoldingCompany,'
	+ '
	' + CASE WHEN @WorkCountry IS NOT NULL THEN 'PH.WorkCountry' ELSE '''Total''' END + ' AS WorkCountry,'
	+ '
	' + CASE WHEN @AgeBand IS NOT NULL THEN 'ABT.AgeBandID,
	' + 'PH.AgeBand' ELSE '0' + ' AS AgeBandID, ' + '''Total'''  END + ' AS AgeBand,'
	+ '
	' + CASE WHEN @Gender IS NOT NULL THEN 'PH.Gender' ELSE '''Total''' END + ' AS Gender,'
	+ '
	' + CASE WHEN @Skale IS NOT NULL THEN 'ST.SkaleID,
	' + 'PH.Skale' ELSE '0' + ' AS SkaleID, ' + '''Total''' END + ' AS Skale,'
	+ '
	' + CASE WHEN @Tenure IS NOT NULL THEN 'TT.TenureID,
	' + 'PH.Tenure' ELSE '0' + ' AS TenureID, ' +  '''Total''' END +  ' AS Tenure,'
	+ '
	' + CASE WHEN @Vol IS NOT NULL THEN 'PL.Voluntary' ELSE '''Total''' END + ' AS Voluntary,'
	+ '
	' + 'SUM(PH.Employees) AS Headcount,
	SUM(COALESCE(PL.Employees,0)) AS Leavers,
	COALESCE(ROUND(CAST(SUM(PL.Employees) AS DECIMAL(12,2)) / CAST(SUM(PH.Employees) AS DECIMAL(12,2)), 3), 0) AS Att
'

SELECT @HoldingCompany = NULL WHERE @HoldingCompany = 'Total'
SELECT @Brand = NULL WHERE @Brand = 'Total'
SELECT @WorkCountry = NULL WHERE @WorkCountry = 'Total'
SELECT @AgeBand = NULL WHERE @AgeBand = 'Total'
SELECT @Gender = NULL WHERE @Gender = 'Total'
SELECT @Skale = NULL WHERE @Skale = 'Total'
SELECT @Tenure = NULL WHERE @Tenure = 'Total'
SELECT @Vol = NULL WHERE @Vol = 'Total'

/************************************************************************************
Generate the Group By clause for the final query. This section is dynamic because
if a null value is passed for a parameter then it is excluded from the Group BY.
************************************************************************************/

DECLARE @SQLGroupBy NVARCHAR(MAX)
SELECT @SQLGroupBy = COALESCE(@SQLGroupBY, '') + 
'GROUP BY
	PH.YearQuarter,'
	+ CASE WHEN @Brand IS NOT NULL THEN ',
	' + 'PH.Brand' ELSE '' END
	+ CASE WHEN @HoldingCompany IS NOT NULL THEN ',
	PH.HoldingCompany' ELSE '' END
	+ CASE WHEN @WorkCountry IS NOT NULL THEN ',
	PH.WorkCountry' ELSE '' END
	+ CASE WHEN @AgeBand IS NOT NULL THEN ',
	ABT.AgeBandID,
	'+ '
	PH.AgeBand' ELSE '' END
	+ CASE WHEN @Gender IS NOT NULL THEN ',
	PH.Gender' ELSE '' END
	+ CASE WHEN @Skale IS NOT NULL THEN ',
	' + 'ST.SkaleID,
	' + 'PH.Skale' ELSE '' END
	+ CASE WHEN @Tenure IS NOT NULL THEN ',
	' + 'TT.TenureID,
	' + 'PH.Tenure' ELSE '' END
	+ CASE WHEN @Vol IS NOT NULL THEN ',
	PL.Voluntary' ELSE '' END

/************************************************************************************
Generate the Order By clause for the final query. This section is dynamic because
if a null value is passed for a parameter then it cannot be used in the Sort By clause.
************************************************************************************/
	
DECLARE @SQLOrderBy NVARCHAR(MAX)
SELECT @SQLOrderBy = COALESCE(@SQLOrderBy, '') + '
ORDER BY
	PH.YearQuarter
	' + CASE WHEN @HoldingCompany IS NOT NULL THEN ',
	' + 'PH.HoldingCompany' ELSE '' END
	 + CASE WHEN @Brand IS NOT NULL THEN ',
	' + 'PH.Brand' ELSE '' END
	+ CASE WHEN @WorkCountry IS NOT NULL THEN ',
	' + 'PH.WorkCountry' ELSE '' END
	+ CASE WHEN @AgeBand IS NOT NULL THEN ',
	ABT.AgeBandID' ELSE '' END
	+ CASE WHEN @Gender IS NOT NULL THEN ',
	PH.Gender' ELSE '' END
	+ CASE WHEN @Skale IS NOT NULL THEN ',
	ST.SkaleID' ELSE '' END
	+ CASE WHEN @Tenure IS NOT NULL THEN ',
	TT.TenureID' ELSE '' END 
SELECT @SQL = @SQL + @SQLFields + @SQLFrom + @SQLGroupBy 
SELECT @SQL = @SQL + @SQLOrderBy

/********************************************************************************
The following section generates a Total row for each month selected.
First, we need to test to see if any parameters have been selected. If no 
parameters have been selected then there is no need to add a row for the 
Total as the results will be for the whole company anyway.
********************************************************************************/

IF COALESCE(@Brand, @AgeBand, @Gender, @Skale, @Tenure, @Vol, '1') <> '1'
BEGIN

/********************************************************************************
Start by stripping the ORDER BY clause from the SQL already generated. This is
because we are going to add the Total row by using the UNION operator.
The UNION operator only allows the ORDER BY clause to be at the end of the query,
so it is added back in later.
********************************************************************************/
SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 1 - (LEN(@SQL) - CHARINDEX('ORDER BY', @SQL)))

/********************************************************************************
Create a temp table to hold the Total Headcount for each month in the date
range.
********************************************************************************/

--IF OBJECT_ID('tempdb..#PITTotalHeadcount') IS NOT NULL DROP TABLE #PITTotalHeadcount
CREATE TABLE #PITTotalHeadcount
(
	YYYYMM INT,
	MonthYear VARCHAR(10),
	Brand VARCHAR(100),
	AgeBandID INT,
	AgeBand NVARCHAR(100),
	Gender VARCHAR(10),
	SkaleID INT,
	Skale NVARCHAR(100),
	TenureID INT,
	Tenure NVARCHAR(100),
	HoldingCompany NVARCHAR(100),
	WorkCountry NVARCHAR(100),
	Employees INT
)

/********************************************************************************
Populate the temp table by running the REP_PITHeacount procedure passing NULL
for all parameter values but using the Date Range passed to the REP_PITAttrition 
procedure.
********************************************************************************/

INSERT INTO #PITTotalHeadcount
EXEC reports.REP_PITFTEHeadcount NULL, NULL, NULL, NULL, NULL, NULL, NULL, @FirstDate, @LastDate	

ALTER TABLE #PITTotalHeadcount
ADD YearQuarter NVARCHAR(6)

UPDATE #PITTotalHeadcount
	SET YearQuarter = DR.YearQuarter
FROM
	#PITTotalHeadcount PTH
	JOIN #DateRange DR ON PTH.MonthYear = DR.MonthYear
/********************************************************************************
Create a temp table to hold the total number of leavers for Kantar overall during
the date range selected. 
********************************************************************************/

--IF OBJECT_ID('tempdb..#PITTotalLeavers') IS NOT NULL DROP TABLE #PITTotalLeavers
CREATE TABLE #PITTotalLeavers
(
	YYYYMM INT,
	MonthYear VARCHAR(10),
	Brand VARCHAR(100),
	HoldingCompany NVARCHAR(100),
	WorkCountry NVARCHAR(100),
	AgeBandID INT,
	AgeBand NVARCHAR(100),
	Gender VARCHAR(10),
	SkaleID INT,
	Skale NVARCHAR(100),
	TenureID INT,
	Tenure NVARCHAR(100),
	Voluntary NVARCHAR(5),
	Employees INT
)

/********************************************************************************
Populate the temp table by running the REP_PITLeavers procedure passing NULL
for all parameter values but using the Date Range passed to the REP_PITAttrition 
procedure.
********************************************************************************/

INSERT INTO #PITTotalLeavers
EXEC reports.REP_PITLeavers NULL, NULL, NULL, NULL, NULL, NULL, NULL, @Vol, @FirstDate, @LastDate

ALTER TABLE #PITTotalLeavers
ADD YearQuarter NVARCHAR(6)

UPDATE #PITTotalLeavers
	SET YearQuarter = DR.YearQuarter
FROM
	#PITTotalLeavers PTL
	JOIN #DateRange DR ON PTL.YYYYMM = DR.YYYYMM
/********************************************************************************
Add the Total rows as a UNION to the existing SQL and then add the
ORDER BY at the very end
********************************************************************************/

SELECT @SQL = @SQL +
'
UNION
	SELECT 
		PTTH.YearQuarter,
		' + '''zTotal''' +',
		' + '''zTotal''' +',
		' + '''zTotal''' +',
		0,
		' + '''Total''' +',
		' + '''Total''' + ',
		0,
		' + '''Total''' +',
		0,
		' + '''Total''' +',
		' + '''Total''' +',
		SUM(PTTH.Employees),
		SUM(PTTL.Employees),
		COALESCE(ROUND(CAST(SUM(PTTL.Employees) AS DECIMAL(12,2)) / CAST(SUM(PTTH.Employees) AS DECIMAL(12,2)), 3),0)
	FROM
		#PITTotalHeadcount PTTH
		LEFT JOIN #PITTotalLeavers PTTL ON PTTH.YearQuarter = PTTL.YearQuarter
	GROUP BY
		PTTH.YearQuarter
	ORDER BY
		1,3'
END

--Uncomment line below to view the generated SQL
PRINT @SQL

--Run the generated SQL
----EXEC sp_executesql @SQL

Open in new window

can you please confirm that you do not get this error
from this sub proc
EXEC reports.REP_PITFTEHeadcount @HoldingCompany, @Brand, @WorkCountry, @AgeBand, @Gender, @Skale, @Tenure, @FirstDate, @LastDate
?
could you please check this proc code?


from this code segment?
--SELECT * FROM #DateRange
INSERT INTO #PITHeadcount
EXEC reports.REP_PITFTEHeadcount @HoldingCompany, @Brand, @WorkCountry, @AgeBand, @Gender, @Skale, @Tenure, @FirstDate, @LastDate

--SELECT * FROM #DateRange

ALTER TABLE #PITHeadcount
ADD YearQuarter VARCHAR(6)
--SELECT * FROM #DateRange
--Why doesn't #DateRange pick up? Invalid Object Name

Open in new window



also: what is your sql server service pack?
Not getting an error from the code segment other than the 'Invalid Object'. Interestingly, if I slim the PROC down into the code below then I still get the error:

SET FMTONLY OFF

DECLARE
	@Brand NVARCHAR(100) = NULL,
	@HoldingCompany NVARCHAR(100) = NULL,
	@WorkCountry NVARCHAR(100) = NULL,
	@AgeBand NVARCHAR(100) = NULL,
	@Gender NVARCHAR(100) = NULL,
	@Skale NVARCHAR(100) = NULL,
	@Tenure NVARCHAR(100) = NULL,
	@FirstDate DATE = '2016-01-01',
	@LastDate DATE = '2016-12-31',
	@Vol NVARCHAR(3) = 'ALL'
	
	
	
IF OBJECT_ID('tempdb..#DateRange') IS NOT NULL DROP TABLE #DateRange
CREATE TABLE #DateRange
(
	ParDate DATE,
	YYYYMM VARCHAR(6),
	MonthYear VARCHAR(10),
	YearQuarter VARCHAR(6)
)
--SELECT statement inserts a value for each month that falls between the first and last date of the parameters
--This allows us to return a value for the beginning of each month.


INSERT INTO #DateRange
SELECT
	DateRange.Date,
	DateRange.YYYYMM,
	DateRange.MonthYear,
	DateRange.YearQuarter
FROM
(
	SELECT DISTINCT
		dD.Date,
		dD.YYYYMM,
		dD.MonthYear,
		CAST(dD.Year AS VARCHAR(4)) + '-' + CAST(dD.Quarter AS Varchar(1)) AS YearQuarter
	FROM
		DimDate dD
	WHERE
		DATEPART(DAY, dD.Date) = 1
		AND dD.Date BETWEEN @FirstDate AND @LastDate
) DateRange


IF OBJECT_ID('tempdb..#PITHeadcount') IS NOT NULL DROP TABLE #PITHeadcount
CREATE TABLE #PITHeadcount
(
	YYYYMM INT,
	MonthYear VARCHAR(10),
	Brand VARCHAR(100),
	AgeBandID INT,
	AgeBand NVARCHAR(100),
	Gender VARCHAR(10),
	SkaleID INT,
	Skale NVARCHAR(100),
	TenureID INT,
	Tenure NVARCHAR(100),
	HoldingCompany NVARCHAR(100),
	WorkCountry NVARCHAR(100),
	Employees INT
)

--SELECT * FROM #DateRange
INSERT INTO #PITHeadcount
EXEC reports.REP_PITFTEHeadcount @HoldingCompany, @Brand, @WorkCountry, @AgeBand, @Gender, @Skale, @Tenure, @FirstDate, @LastDate



ALTER TABLE #PITHeadcount
ADD YearQuarter VARCHAR(6)
--SELECT * FROM #DateRange
--Why doesn't #DateRange pick up? Invalid Object Name
UPDATE #PITHeadcount
	SET YearQuarter = dD.YearQuarter
FROM
	#PITHeadcount PH
	JOIN #DateRange dD ON PH.YYYYMM = dD.YYYYMM
	
SET FMTONLY ON

Open in new window


Is it something to do with altering the temp table definition that could cause it?
The SQL version is 2008 R2 SP2.
I've tested your code and could get the error.
The only thing I'm missing is the call to reports.REP_PITFTEHeadcount that I've replaced with a local SP so this is the only thing different. Maybe it helps if you can post the reports.REP_PITFTEHeadcount definition.
Here it is:

ALTER PROCEDURE [reports].[REP_PITFTEHeadcount]
	--Input parameters
	--DECLARE
	@HoldingCompany NVARCHAR(100)= NULL, --Added
	@Brand NVARCHAR(100) = NULL,
	--@SubCompany NVARCHAR(100) = NULL, --Added
	@WorkCountry NVARCHAR(100) = NULL, --Added
	@AgeBand NVARCHAR(100) = NULL,
	@Gender NVARCHAR(100) = NULL,
	@Skale NVARCHAR(100) = NULL,
	@Tenure NVARCHAR(100) = NULL,
	@FirstDate DATE,
	@LastDate DATE
AS

SET NOCOUNT ON
--Declare variable to hold final SQL which will be executed at end of procedure to generate results
DECLARE @SQL NVARCHAR(MAX)
--Following section is used for testing only	
--SELECT @Brand = NULL,
--	@AgeBand = NULL,
--	@Gender = NULL,
--	@Skale = 'ALL',
--	@Tenure = 'ALL',
--	@FirstDate = '2015-09-01',
--	@LastDate = '2016-08-31'

--Create a temp table to hold the brands that have been passed to the procedure
IF OBJECT_ID('tempdb..#BrandTable')	IS NOT NULL DROP TABLE #BrandTable
CREATE TABLE #BrandTable 
(
	NAME VARCHAR(100)
)


--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'HQ,TS'
INSERT INTO #BrandTable
	(NAME)
SELECT 
	C.NAME
FROM 
	COMPANY C  
WHERE   
	CHARINDEX(',' +  C.NAME + ',',','+ REPLACE(@Brand, ', ',',')+',') > 0  
	
UNION 
-- Second SELECT statement add all Brands from COMPANY table if 'ALL' has been passed as a parameter value
SELECT 
	NAME   
FROM
	COMPANY C  
WHERE 
	@Brand = 'ALL'
	
UNION
--If no value has been passed for the Brand parameter, then 'Total' is added to Brand temp table. This will sum the value across all brands
SELECT
	'Total'
WHERE 
	@Brand is null

--Create a temp table to hold the Holding Companies that have been passed to the procedure
IF OBJECT_ID('tempdb..#HoldingcompanyTable')	IS NOT NULL DROP TABLE #HoldingCompanyTable
CREATE TABLE #HoldingCompanyTable 
(
	HoldingCompany VARCHAR(100)
)
DELETE #HoldingCompanyTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'HQ,NS'
INSERT INTO #HoldingCompanyTable
	(HoldingCompany)
SELECT 
	HC.NAME
FROM 
	HOLDINGCOMPANY HC  
WHERE   
	CHARINDEX(',' +  HC.NAME + ',',','+ REPLACE(@HoldingCompany, ', ',',')+',') > 0  
	
UNION 
-- Second SELECT statement add all Brands from COMPANY table is 'ALL' has been passed as a parameter value
SELECT 
	HC.NAME   
FROM
	HOLDINGCOMPANY HC  
WHERE 
	@HoldingCompany = 'ALL'
	
UNION
--If no value has been passed for the Brand parameter, then 'Total' is added to Brand temp table. This will sum the value across all brands
SELECT
	'Total'
WHERE 
	@HoldingCompany is null
	
--Create a temp table to hold the Countries that have been passed to the procedure
IF OBJECT_ID('tempdb..#CountryTable')	IS NOT NULL DROP TABLE #CountryTable
CREATE TABLE #CountryTable 
(
	Country VARCHAR(100)
)
DELETE #CountryTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'United Kingdom,France'
INSERT INTO #CountryTable
	(Country)
SELECT 
	OPV.DISPLAYVALUE
FROM
	COUNTRY CO
	JOIN OC_PICKLISTVALUES OPV ON CO.COUNTRY = OPV.STOREVALUE
	AND OPV.CULTURECODE = 'en-gb'
	AND OPV.PICKLISTID = 'F1DAC2DD-B4BC-4FA8-8E7D-8B5E5D7E00AE' 
WHERE   
	CHARINDEX(',' +  OPV.DISPLAYVALUE + ',',','+ REPLACE(@WorkCountry, ', ',',')+',') > 0  
	
UNION 
-- Second SELECT statement add all Brands from COMPANY table is 'ALL' has been passed as a parameter value
SELECT
	OPV.DISPLAYVALUE
FROM
	COUNTRY CO
	JOIN OC_PICKLISTVALUES OPV ON CO.COUNTRY = OPV.STOREVALUE
	AND OPV.CULTURECODE = 'en-gb'
	AND OPV.PICKLISTID = 'F1DAC2DD-B4BC-4FA8-8E7D-8B5E5D7E00AE'
WHERE
	CO.COUNTRY_ID IN
	(
		SELECT DISTINCT
			PO.COUNTRYNAME
		FROM
			POST PO
		WHERE
			CO.COUNTRY_ID = PO.COUNTRYNAME
			
	) 
AND @WorkCountry = 'ALL'
	
UNION
--If no value has been passed for the Brand parameter, then 'Total' is added to Brand temp table. This will sum the value across all brands
SELECT
	'Total'
WHERE 
	@WorkCountry is null

--Create a temp table to hold the Age Bands that have been passed to the procedure
IF OBJECT_ID('tempdb..#AgeBandTable') IS NOT NULL DROP TABLE #AgeBandTable
CREATE TABLE #AgeBandTable
(
	AgeBandID INT,
	NAME NVARCHAR(100)
)

DELETE #AgeBandTable
--First SELECT statement parses the parameter if multiple values have been passed, i.e. '16-25,26-35'
INSERT INTO #AgeBandTable
	(AgeBandID, NAME)
SELECT
	dAB.AgeBandID,
	dAB.AgeBand
FROM
	dimAgeBands dAB
WHERE
	CHARINDEX(',' + dAB.AgeBand + ',',',' + REPLACE(@AgeBand, ', ',',') + ',') > 0
	
UNION
-- Second SELECT statement add all Age Bands from dimAgeBands table is 'ALL' has been passed as a parameter value
SELECT
	dAB.AgeBandID,
	dAB.AgeBand
FROM
	dimAgeBands dAB
WHERE
	@AgeBand = 'ALL'
	
UNION
--If no value has been passed for the AgeBand parameter, then 'Total' is added to AgeBand temp table. This will sum the value across all age bands
SELECT
	1,
	'Total'
WHERE
	@AgeBand IS NULL

--Create a temp table to hold the Genders that have been passed to the procedure
IF OBJECT_ID('tempdb..#GenderTable') IS NOT NULL DROP TABLE #GenderTable
CREATE TABLE #GenderTable
(
	Gender VARCHAR(10)
)

DELETE #GenderTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. 'F,M'
INSERT INTO #GenderTable
(Gender)
SELECT
	DS.Item
FROM
	dbo.DelimitedSplit8K(@Gender, ',') DS
	
UNION 
-- Second SELECT statement adds both genders if 'ALL' has been passed as a parameter value
SELECT
	DS.Item
FROM
	dbo.DelimitedSplit8K('M,F,Unknown',',') DS
WHERE
	@Gender = 'ALL'
	
UNION
--If no value has been passed for the Gender parameter, then 'Total' is added to Gender temp table. This will sum the value across both genders
SELECT
	'Total'
WHERE
	@Gender IS NULL

--Create a temp table to hold the Skale Positions that have been passed to the procedure
IF OBJECT_ID('tempdb..#SkaleTable') IS NOT NULL DROP TABLE #SkaleTable
CREATE TABLE #SkaleTable
(
	SkaleID INT,
	Skale NVARCHAR(100)
)

DELETE #SkaleTable

--First SELECT statement parses the parameter if multiple values have been passed, i.e. '80,90'
INSERT INTO #SkaleTable
(SkaleID, Skale)
SELECT
	dSO.SkaleGroupingOrder,
	dSO.SkaleGrouping
FROM
	reports.dimSkaleOrder dSO
WHERE
	CHARINDEX(',' + dSO.SkaleGrouping + ',',',' + REPLACE(@Skale, ', ',',') + ',') > 0
	
UNION
-- Second SELECT statement adds all skale positions from dimSkaleOrder table if 'ALL' has been passed as a parameter value
SELECT
	dSO.SkaleGroupingOrder,
	dSO.SkaleGrouping
FROM
	reports.dimSkaleOrder dSO
WHERE
	@Skale = 'ALL'

UNION
--If no value has been passed for the Skale parameter, then 'Total' is added to Skale temp table. This will sum the value across Skale Positions
SELECT
	1,
	'Total'
WHERE
	@Skale IS NULL

IF OBJECT_ID('tempdb..#TenureTable') IS NOT NULL DROP TABLE #TenureTable
--Create a temp table to hold the Skale Positions that have been passed to the procedure
CREATE TABLE #TenureTable
(
	TenureID INT,
	Tenure NVARCHAR(100)
)

DELETE #TenureTable
--First SELECT statement parses the parameter if multiple values have been passed, i.e. '<2,2-5'
INSERT INTO #TenureTable
(TenureID, Tenure)
SELECT
	dT.dimTenure_ID,
	dT.TenureBand
FROM
	dimTenure dT
WHERE
	CHARINDEX(',' + dT.TenureBand + ',',',' + REPLACE(@Tenure, ', ',',') + ',') > 0
	
UNION
-- Second SELECT statement adds all Tenure bands from dimTenure table if 'ALL' has been passed as a parameter value
SELECT
	dT.dimTenure_ID,
	dT.TenureBand
FROM
	dimTenure dT
WHERE
	@Tenure = 'ALL'
	
UNION
--If no value has been passed for the Tenure parameter, then 'Total' is added to Skale temp table. This will sum the value across Tenure Bands
SELECT
	1,
	'Total'
WHERE
	@Tenure IS NULL

--Create a temp table to hold the date range parameters passed to the procedure
--This is done so that we can convert to the first date of the month for the first date parameter,
--and the last date of the month for the last date parameter
IF OBJECT_ID('tempdb..#DateRange') IS NOT NULL DROP TABLE #DateRange
CREATE TABLE #DateRange
(
	ParDate DATE,
	YYYYMM VARCHAR(6),
	MonthYear VARCHAR(10)
)
--SELECT statement inserts a value for each month that falls between the first and last date of the parameters
--This allows us to return a value for the beginning of each month.
INSERT INTO #DateRange
SELECT
	DateRange.Date,
	DateRange.YYYYMM,
	DateRange.MonthYear
FROM
(
	SELECT DISTINCT
		dD.Date,
		dD.YYYYMM,
		dD.MonthYear
	FROM
		DimDate dD
	WHERE
		DATEPART(DAY, dD.Date) = 1
		AND dD.Date BETWEEN @FirstDate AND @LastDate
) DateRange

--Construct SQL

--First we set up the FROM clause for the final SQL statement. This is static and always uses the same tables
DECLARE @SQLFrom NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL, '') + 'SELECT DISTINCT 
	'

SELECT @SQLFrom = COALESCE(@SQLFrom, '') + 'FROM
	cubeHeadCountFTEPIT cHCP
	JOIN #DateRange DR ON cHCP.dMonth = DR.YYYYMM
	JOIN #BrandTable BT ON cHCP.Brand = BT.NAME
	JOIN #AgeBandTable ABT ON cHCP.AgeBand = ABT.NAME
	JOIN #GenderTable GT ON cHCP.Gender = GT.Gender
	JOIN #SkaleTable ST ON cHCP.Skale = ST.Skale
	JOIN #TenureTable TT ON cHCP.Tenure = TT.Tenure
	JOIN #HoldingCompanyTable HC ON cHCP.HoldingCompany = HC.HoldingCompany
	JOIN #CountryTable CT ON cHCP.WorkCountry = CT.Country
'

--Next we setup the fields that will be returned. This is dynamic because the fields are dependent on the parameters that
--are passed. For example, if a parameter is NULL then the text value 'Total' is returned instead of a record from a table
DECLARE @SQLFields NVARCHAR(MAX)
SELECT @SQLFields = COALESCE(@SQLFields, '') + 
	'dR.YYYYMM,
	dR.MonthYear,
	' + CASE WHEN @Brand IS NOT NULL THEN 'cHCP.Brand' ELSE '''Total'''  END + ' AS Brand,'
	+ '
	' + CASE WHEN @AgeBand IS NOT NULL THEN 'ABT.AgeBandID,
	' + 'cHCP.AgeBand' ELSE '0' + ' AS AgeBandID, ' + '''Total'''  END + ' AS AgeBand,'
	+ '
	' + CASE WHEN @Gender IS NOT NULL THEN 'cHCP.Gender' ELSE '''Total''' END + ' AS Gender,'
	+ '
	' + CASE WHEN @Skale IS NOT NULL THEN 'ST.SkaleID,
	' + 'CASE WHEN cHCP.Skale = ''99'' THEN ''Awaiting Skale'' ELSE cHCP.Skale END' ELSE '0' + ' AS SkaleID, ' + '''Total''' END + ' AS Skale,'
	+ '
	' + CASE WHEN @Tenure IS NOT NULL THEN 'TT.TenureID,
	' + 'cHCP.Tenure' ELSE '0' + ' AS TenureID, ' +  '''Total''' END +  ' AS Tenure,'
	+ '
	' + CASE WHEN @HoldingCompany IS NOT NULL THEN 'cHCP.HoldingCompany' ELSE '''Total''' END + ' AS HoldingCompany,'
	+ '
	' + CASE WHEN @WorkCountry IS NOT NULL THEN 'cHCP.WorkCountry' ELSE '''Total''' END + ' AS WorkCountry,'
	+ '
	' + 'SUM(cHCP.Employees) AS Employes 
'

--Reset the parameter values to NULL where they have been set to total
SELECT @Brand = NULL WHERE @Brand = 'Total'
SELECT @AgeBand = NULL WHERE @AgeBand = 'Total'
SELECT @Gender = NULL WHERE @Gender = 'Total'
SELECT @Skale = NULL WHERE @Skale = 'Total'
SELECT @Tenure = NULL WHERE @Tenure = 'Total'
SELECT @HoldingCompany = NULL WHERE @HoldingCompany = 'Total'
SELECT @WorkCountry = NULL WHERE @WorkCountry = 'Total'

--Setup the GROUP BY section of the SQL. This is dynamic for the same reason as the Fields. 
--If Null is passed to a parameter, then we do not need to Group by that field.
DECLARE @SQLGroupBy NVARCHAR(MAX)
SELECT @SQLGroupBy = COALESCE(@SQLGroupBY, '') + 
'GROUP BY
	dR.YYYYMM,
	dR.MonthYear'
	+ CASE WHEN @Brand IS NOT NULL THEN ',
	' + 'cHCP.Brand' ELSE '' END
	+ CASE WHEN @AgeBand IS NOT NULL THEN ',
	ABT.AgeBandID,
	'+ '
	cHCP.AgeBand' ELSE '' END
	+ CASE WHEN @Gender IS NOT NULL THEN ',
	cHCP.Gender' ELSE '' END
	+ CASE WHEN @Skale IS NOT NULL THEN ',
	' + 'ST.SkaleID,
	' + 'cHCP.Skale' ELSE '' END
	+ CASE WHEN @HoldingCompany IS NOT NULL THEN ',
	' + 'cHCP.HoldingCompany' ELSE '' END
	+ CASE WHEN @WorkCountry IS NOT NULL THEN ',
	' + 'cHCP.WorkCountry' ELSE '' END
	+ CASE WHEN @Tenure IS NOT NULL THEN ',
	' + 'TT.TenureID,
	' + 'cHCP.Tenure' ELSE '' END

--Setup the ORDER BY Section of the SQL. This is dynamic because we can only order by
--fields that are actually returned by the query.
DECLARE @SQLOrderBy NVARCHAR(MAX)
SELECT @SQLOrderBy = COALESCE(@SQLOrderBy, '') + '
ORDER BY
	dR.YYYYMM
	' + CASE WHEN @HoldingCompany IS NOT NULL THEN ',
	' + 'cHCP.HoldingCompany' ELSE '' END
	+ CASE WHEN @Brand IS NOT NULL THEN ',
	' + 'cHCP.Brand' ELSE '' END
	+ CASE WHEN @WorkCountry IS NOT NULL THEN ',
	cHCP.WorkCountry' ELSE '' END
	+ CASE WHEN @AgeBand IS NOT NULL THEN ',
	ABT.AgeBandID' ELSE '' END
	+ CASE WHEN @Gender IS NOT NULL THEN ',
	cHCP.Gender' ELSE '' END
	+ CASE WHEN @Skale IS NOT NULL THEN ',
	ST.SkaleID DESC' ELSE '' END
	+ CASE WHEN @Tenure IS NOT NULL THEN ',
	TT.TenureID' ELSE '' END 
	
--Concatenate the SQL variables to construct the final query
SELECT @SQL = @SQL + @SQLFields + @SQLFrom + @SQLGroupBy 
SELECT @SQL = @SQL + @SQLOrderBy

--To test the final SQL query, uncomment line below
--PRINT @SQL

EXEC sp_executesql @SQL

SET NOCOUNT OFF

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
And there it is! A simple answer as it turns out. Thanks Eugene Z; I didn't realise that the temp tables in another Proc would cause issues when called from another proc using the same names. It makes sense I suppose. As you can tell, I based the latest proc on the one that is being called with a few changes.
Did you run my samples??? They show you exactly that kind of problem..