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
Best Answer
I have been there trust me, to resolve this issue try modifying accordingly and then add this code in applicable places in your Stored Procedures: