帮忙看段sql

[a284280221]

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROCEDURE CROSSTAB
AS
BEGIN
	DECLARE @AREANAME VARCHAR(50)
	DECLARE @F_CLASS2 VARCHAR(50)
	DECLARE @AR_MONEY DECIMAL(18,2)
	DECLARE @CREATESQL VARCHAR(200)
	DECLARE @INSERTSQL VARCHAR(200)

	CREATE TABLE #TMP(项目 VARCHAR(50))
	DECLARE CREATECURSOR CURSOR FOR SELECT DISTINCT AREANAME FROM DINING_AREA
	DECLARE INSERTCURSOR CURSOR FOR SELECT ISNULL(SUM(AR_MONEY),0) AS AR_MONEY,F_CLASS2,AREANAME FROM (SELECT ISNULL(SUM(AR_MONEY),0) AS AR_MONEY,F_CLASS2,(SELECT AREANAME FROM DINING_AREA WHERE ID=(SELECT AREAID FROM DISHTABLE WHERE ID=(SELECT TABLEID FROM TABLEORDERS WHERE ID=(SELECT TABLEORDERSID FROM ORDERDISH WHERE ID=O.ID)))) AS AREANAME FROM ORDERDISH O GROUP BY F_CLASS2,ID) A GROUP BY F_CLASS2,AREANAME ORDER BY F_CLASS2,AREANAME
	OPEN CREATECURSOR
	FETCH NEXT FROM CREATECURSOR INTO @AREANAME
	WHILE @@FETCH_STATUS=0
	BEGIN
		IF NOT @AREANAME IS NULL
		BEGIN
			SET @CREATESQL='ALTER TABLE #TMP ADD '+@AREANAME+' DECIMAL(18,2)'
			EXEC(@CREATESQL)
			
		END
		FETCH NEXT FROM CREATECURSOR INTO @AREANAME
	END

	OPEN INSERTCURSOR	
	FETCH NEXT FROM INSERTCURSOR INTO @AR_MONEY,@F_CLASS2,@AREANAME
			WHILE @@FETCH_STATUS=0
			BEGIN
				SET @INSERTSQL='INSERT INTO #TMP (项目,'+ @AREANAME +') VALUES('''+ @F_CLASS2 +''','+ @AR_MONEY +')'
				EXEC(@INSERTSQL)
				FETCH NEXT FROM INSERTCURSOR INTO @AR_MONEY,@F_CLASS2,@AREANAME
			END
	CLOSE CREATECURSOR
	DEALLOCATE CREATECURSOR
	CLOSE INSERTCURSOR
	DEALLOCATE INSERTCURSOR

END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[开着拖拉机泡妞]

看了然后呢？

[孤独加百列]

把#tmp改成##tmp

[a284280221]

将数据类型 varchar 转换为 numeric 时出错。

报这个错 找不出来问题 折磨我一天了

谢谢 帮忙看下

[a284280221]

SET @INSERTSQL='INSERT INTO #TMP (项目,'+ @AREANAME +') VALUES('''+ @F_CLASS2 +''','+ @AR_MONEY +')'

这句有问题

[开着拖拉机泡妞]

SET @INSERTSQL='INSERT INTO #TMP (项目,'+ ltrim（@AREANAME) +') VALUES('''+ ltrim(@F_CLASS2 )+''','+ltrim( @AR_MONEY )+')'

[a284280221]

感谢大虾