关于临时表
背景：Silverlight项目，通过WCF连接数据库，调用存储过程获取数据。
具体proc：
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('up_RetrieveIDC_V2','P') IS NOT NULL
DROP PROCEDURE up_RetrieveIDC_V2
GO
CREATE PROCEDURE [dbo].[up_RetrieveIDC_V2]
@NAMENVARCHAR(200),
@BANDWIDTH_TYPEINT,
@STATEINT
AS
BEGIN
DECLARE @SQL VARCHAR(1000);
/*创建临时表*/
SELECT IDC_ID,COUNT(ID) AS CABINET_COUNT INTO ##TBL_CABINET_COUNT FROM IDC_CABINET GROUP BY IDC_ID
/*拼接SQL*/
SET @SQL= ' SELECT
C.IDC_ID,C.NAME,C.LOCATION,
C.BANDWIDTH_TOTAL,B.[TYPE_NAME],
SP.SP_NAME,TCC.CABINET_COUNT AS Cabinet_Count,
SP.BIZ_MAN,SP.BIZ_TEL,
C.TECH_MAN,C.TECH_MAN_TEL,S.STATE_NAME,C.REMARK,C.IDC_TEL,C.CABINET_PRICE,C.BANDWIDTH_PRICE
FROM IDC AS C
LEFT JOIN IDC_STATE AS S ON C.IDC_STATE=S.STATE_ID
LEFT JOIN BANDWIDTH_TYPE AS B ON C.BANDWIDTH_TYPE=B.TYPE_ID
LEFT JOIN IDC_SP AS SP ON C.SP_ID=SP.SP_ID
LEFT JOIN ##TBL_CABINET_COUNT AS TCC ON TCC.IDC_ID=C.IDC_ID WHERE 1=1 '
IF(@NAME<>'' AND @NAME IS NOT NULL)
SET @SQL=@SQL+' AND NAME LIKE ''%'+@NAME+'%'''
IF(@BANDWIDTH_TYPE<>'' AND @BANDWIDTH_TYPE IS NOT NULL)
SET @SQL=@SQL+' AND BANDWIDTH_TYPE='+CONVERT(VARCHAR(30),@BANDWIDTH_TYPE)+' '
IF(@STATE<>'' AND @STATE IS NOT NULL)
SET @SQL=@SQL+' AND IDC_STATE='+CONVERT(VARCHAR(30),@STATE)+' '
SET @SQL=@SQL+' ORDER BY C.IDC_ID DESC'
PRINT @SQL
EXEC(@SQL)
DROP TABLE ##TBL_CABINET_COUNT
END
AS
BEGIN
SET NOCOUNT ON -- Try !
DECLARE @SQL VARCHAR(1000);
/*创建临时表*/
SELECT IDC_ID,COUNT(ID) AS CABINET_COUNT INTO ##TBL_CABINET_COUNT FROM IDC_CABINET GROUP BY IDC_ID
