改为如下,不需要使用动态SQL。
DECLARE @SDTE DATETIME
SET @SDTE='2005-03-30 12:00:00'select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T
WHERE SDTE >@SDTE
Group by DEPT,PKG,LOT with rollup
DECLARE @SDTE DATETIME
SET @SDTE='2005-03-30 12:00:00'select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T
WHERE SDTE >@SDTE
Group by DEPT,PKG,LOT with rollup
SET @SDTE='2005-03-30 12:00:00'select DEPT,PKG,LOT,TOTAL=sum(NQTY) from T
WHERE SDTE >@SDTE
Group by DEPT,PKG,LOT with rollup
WHERE A.CODE = B.CODE
中间这段SQL有问题,所以我没有加上来
----
WHERE SDTE >'''+convert(nvarchar(22),@SDTE,120)+'''
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SET @SDTE='2005-03-30 12:00:00'
SELECT @SQL= @SQL+ ','--樓主沒有發再每次循環多了一個,嗎你可以用print(@SQL)調試。看和表態是一樣沒有﹐有沒有語法錯誤
+quotename(B.NAME,'''')+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')+' THEN NQTY END),0)' FROM T A(NOLOCK),CODENAME B(NOLOCK)
WHERE A.CODE = B.CODE set @SQL = 'select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T
WHERE SDTE >'''+@SDTE+'''
Group by DEPT,PKG,LOT with rollup'
exec(@SQL)至于樓主說的那個問題應該是字符和日期型之間的問題
多注意一下
會出錯的
我以前出過這樣的問題
如select ,......之類
不知道我說清楚了沒有
WHERE SDTE >'''+@SDTE+'''
Group by DEPT,PKG,LOT with rollup'绝对还是错的。