SQL Server 2012 SSRS:使用全局临时表创建报表

Question

我正在使用存储过程创建一个SSRS报告。存储过程进行几次动态旋转。中间表的模式是动态的，因此我使用临时表。由于这个过程使用了动态旋转，所以我也很少使用动态SQL;因此不能在我的脚本中使用本地临时表。存储过程运行正常;但是，当将相同的存储过程嵌入到SSRS查询设计器中时，它会给出全局临时表已经存在的错误。更不用说，我将在代码的最后删除所有这些。

请查找代码:

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

这段代码容易出错有几个原因。

1. 通过使用同名的全局临时表，这个过程不能并行运行(不能有重叠的执行)。第二次迭代将尝试创建已经存在的表。
2. 将DROP TABLE语句放在最后而不处理错误意味着如果这个过程由于任何原因失败，DROP TABLE语句将不会全部执行，将临时表留在下次执行的道路上。这就是你看到的抛出错误的原因。

要解决这个问题(因为你已经在做动态SQL了)，你可以做的是用在过程开始时确定的单个后缀动态命名表，像这样:

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

然后为每个表加上后缀:

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' 

您需要动态创建每个全局临时表。只要不在查询设计器中打开过程，SSRS应该可以使用动态SQL。一定要避免将过程的DDL作为内联报表SQL。