SQL Server 2012 SSRS: Creating a report using global temporary tables

Question

I am creating a SSRS report using a Stored procedure. The stored procedure does dynamic pivoting couple of times. The schema of the intermediate tables are dynamic and thus I'm using temporary tables. Since the procedure uses dynamic pivoting, I' using little of dynamic SQL as well; Therefore can't use local temporary table in my script. The stored procedure is running fine; but when the same stored procedure is embedded inside a SSRS query designer, it gives the error that the global temporary table already exists.Not to mention, I'm dropping all of them in the end in the code.

please find the code :

USE [IGD_HISTORY_COMBINED]
GO
/****** Object:  StoredProcedure [dbo].[USP_GETIGDDETAILS]    Script Date: 03-10-2016 12:05:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_GETIGDDETAILS]  @ID CHAR(17) ,@YEAR INT=null
AS 
BEGIN 




--declare  @ID CHAR(17)
--set @id='1208700418b8b9c44'



SELECT id, floodzone, FloodzoneLastUpdatedDate, versionid INTO ##HIST  
FROM IGD_HISTORY_MONROE
WHERE ID=@ID and floodzone is not null and RecordStatus='U'  
--and  VersionId BETWEEN 587 AND 748
ORDER BY VERSIONID desc

Select *, 'VERSION_'+ cast( ROW_NUMBER () over (order by VERSIONID) AS varchar)
as New_VERSION into ##temp from ##HIST ORDER BY VERSIONID ASC


--Dynamic Pivoting in order to put the Id and versionid values relational

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
+ QUOTENAME(New_VERSION)
FROM (SELECT DISTINCT New_VERSION FROM ##temp) AS Courses



--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'select * into ##Final from (SELECT ID, FLOODZONE, New_Version' +'
FROM ##Temp)SRC
PIVOT(MAX(FLOODZONE) 
FOR New_vERSION IN (' + @ColumnName + ')) AS PVTTable'


EXEC (@DynamicPivotQuery)


select a.*,b.ParcelIDActual, b.PropertyLocationStreet1,b.PropertyLocationStreet2,
b.PropertyCity,b.PropertyZip, B.CountyName
INTO ##CE
from ##Final a
join IGD_DEV_2012..igd b 
on a.id=b.id;

select * from ##CE;


WITH CTE_New as  (
select 
case when right (C.SourceFileName,8) like '[0-9]%'
Then C.SourceFileName 
When right (C.SourceFileName,8) not like '[0-9]%'
Then c.SourceFileName + '_'+ replace(cast(cast(C.CompletedTime as date) as varchar),'-','')
end as  SourceFileName,
 c.SourceVersion,
'Version_'+SourceVersion as Version1
 from
(select distinct versionid from ##temp) D
join igd_datasourcestatus C 
on d.versionid=c.SourceVersion
)
select VERSION1 +': '+upper(SourceFileName) as Version_information  into ##version_info from CTE_New;





DECLARE @DynamicPivotQuery1 AS NVARCHAR(MAX)
DECLARE @ColumnName1 AS NVARCHAR(MAX);


SELECT Version_information , 'VERSION'+ CAST(ROW_NUMBER () OVER ( ORDER BY Version_information) AS varchar)  vERSIONS
INTO ##TE
FROM ##version_info


--Get distinct values of the PIVOT Column 
SELECT @ColumnName1= ISNULL(@ColumnName1 + ',','') 
+ QUOTENAME(VERSIONS)
FROM (SELECT DISTINCT VERSIONS FROM ##TE) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery1 = 
N'select * into ##Final1 from (SELECT   Version_information, versions' +'
FROM ##TE)SRC
PIVOT(MAX(version_information) 
FOR versions IN (' + @ColumnName1 + ')) AS PVTTable'


EXEC sp_executesql @DynamicPivotQuery1

truncate table new1


SELECT A.*,B.* 
into ##TEMPO
FROM ##CE A 
JOIN ##FINAL1 B
ON A.ID = @ID 


 if exists (select  max(versions) from ##te having max(versions)='version7')
 begin 
 insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]
      ,[version7]
      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]
      ,[version_7])
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]
      ,[version7]
      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]
      ,[version_7]
      from ##TEMPO 
end

 if exists (select  max(versions) from ##te having max(versions)='version6')
 Begin 
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]
     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]

      from ##TEMPO 
end

if exists (select max(versions) from ##te having max(versions)='version5')
 Begin 
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]

      from ##TEMPO 
end

if exists (select  max(versions) from ##te having max(versions)='version4')
Begin
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]

     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3],
       [version4]
  ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3],
       [version_4]
  from ##TEMPO 

end


if exists (select  max(versions) from ##te having max(versions)='version3')
Begin
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]


      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]


     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]

     from ##TEMPO 

end

if exists (select  max(versions) from ##te having max(versions)='version2')
Begin
  insert new1 ( [id]
      ,[version1]
      ,[version2]


      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]



     )
 select  [id]
      ,[version1]
      ,[version2]


      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]


     from ##TEMPO 

end


if exists (select  max(versions) from ##te having max(versions)='version1')
Begin
  insert new1 ( [id]
      ,[version1]



      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]




     )
 select  [id]
      ,[version1]
       ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]


     from ##TEMPO 

end









Drop table ##TEMPO
drop table ##Final
DROP TABLE ##CE
DROP TABLE ##HIST
DROP TABLE ##TE
DROP TABLE ##temp
DROP TABLE ##version_info
DROP TABLE ##Final1



select * from NEW1


END

Answer 1

There are a couple of reasons this code is failure-prone.

1. By using global temp tables with the same name, this procedure can't run in parallel (can't have overlapping executions). The second iteration will try to create tables that already exist.
2. Placing the DROP TABLE statements at the end with no error handling means if this procedure fails for any reason, the DROP TABLE statements won't all execute, leaving temp tables in the way of the next execution. That's what's throwing the error you're seeing.

What you could do to fix this (since you're doing dynamic SQL already) is to dynamically name the tables with a single suffix determined at the beginning of the procedure, like this:

DECLARE @TableSuffix NVARCHAR(10))
SET @TableSuffix = CAST(ABS(CHECKSUM(NewId())) % 9999999999 AS NVARCHAR(10));

And then for each table, tack on the suffix:

SET @DynamicPivotQuery = 
N'select * into ##Final' + @TableSuffix + 
N' from 
    (SELECT ID, FLOODZONE, New_Version' +'
     FROM ##Temp' + @TableSuffix + N')SRC
     PIVOT(MAX(FLOODZONE) 
     FOR New_vERSION IN (' + @ColumnName + ')) AS PVTTable' 

You'll need to make each global temp table's creation dynamic. SSRS should be okay with dynamic SQL as long as you don't open the procedure the Query Designer. Definitely avoid having a procedure's DDL as inline report SQL.