2次使用动态SQL

[vbsnake]

DECLARE @SQL VARCHAR(8000)
DECLARE @ESQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL= @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 GROUP BY A.CODE,B.NAME
Set @ESQL = '
SELECT P.DEPT,P.PKG,P.LOT'+@SQL+' FROM (
SELECT * FROM (
select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup
) G LEFT OUTER JOIN (SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5
FROM T(NOLOCK) WHERE ) H ON G.LOT=H.HLOT ) P '
Exec(@ESQL)

问题出现在‘SELECT P.DEPT,P.PKG,P.LOT'+@SQL+' FROM (’
错误消息是
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'NQTY'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'CODE'.
怎么办呢？

[wyb0026]

@ESQL的语法不正确如果可能把表结构贴出来看一下

[子陌红尘]

很显然，
SELECT * FROM
(select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup ) G
LEFT OUTER JOIN
(SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5
FROM T(NOLOCK) WHERE ) H
ON
G.LOT=H.HLOT

返回的结果集中已经没有了 NQTY和CODE字段

[子陌红尘]

TRY:
-----------------------------------------------------------
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @ESQL VARCHAR(8000)

SET @SQL1=''
SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'''')+''
FROM T A(NOLOCK),Codename B(NOLOCK) WHERE A.CODE = B.CODE GROUP BY A.CODE,B.NAME

SET @SQL2=''
SELECT @SQL2= @SQL2+ ','+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 GROUP BY A.CODE,B.NAME

Set @ESQL = '
SELECT P.DEPT,P.PKG,P.LOT'+@SQL1+' FROM (
SELECT * FROM (
SELECT DEPT,PKG,LOT'+@SQL2+',TOTAL=sum(NQTY) from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup ) G
LEFT OUTER JOIN
(SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5 FROM T(NOLOCK) WHERE ) H
ON G.LOT=H.HLOT ) P '
Exec(@ESQL)

[ITpassport]

SET @SQL1=''
SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'''')+''
应该是
SET @SQL1=''
SELECT @SQL1= @SQL1+ ','+quotename(B.NAME,'')+''

[zjcxc]

错误提示已经很清楚了。

[zjcxc]

--改成这样:

DECLARE @SQL VARCHAR(8000)
DECLARE @ESQL VARCHAR(8000)
DECLARE @S VARCHAR(8000)

SELECT @SQL='',@s=''
SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''')
+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')
+' THEN NQTY END),0)',
@s=@s+',g.'+quotename(B.NAME,'''')
FROM T A(NOLOCK),Codename B(NOLOCK)
WHERE A.CODE = B.CODE
GROUP BY A.CODE,B.NAME
Set @ESQL = '
SELECT P.DEPT,P.PKG,P.LOT'+@s+' FROM (
SELECT * FROM (
select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY)
from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup
) G
LEFT OUTER JOIN (
SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5
FROM T(NOLOCK) WHERE
) H ON G.LOT=H.HLOT
P '
Exec(@ESQL)

[zjcxc]

DECLARE @SQL VARCHAR(8000)
DECLARE @ESQL VARCHAR(8000)
DECLARE @S VARCHAR(8000)

SELECT @SQL='',@s=''
SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''')
+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')
+' THEN NQTY END),0)',
@s=@s+',g.'+quotename(B.NAME,'''')
FROM T A(NOLOCK),Codename B(NOLOCK)
WHERE A.CODE = B.CODE
GROUP BY A.CODE,B.NAME
Set @ESQL = '
SELECT P.DEPT,P.PKG,P.LOT'+@s+' FROM (
SELECT * FROM (
select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY)
from T(NOLOCK)
Group by DEPT,PKG,LOT with rollup
) G
LEFT OUTER JOIN (
SELECT LOT AS HLOT,LOTNAME,STOCK1,STOCK2,STOCK3,STOCK4,STOCK5
FROM T(NOLOCK) WHERE 1=1 --*************这里的条件也不对
) H ON G.LOT=H.HLOT
P '
Exec(@ESQL)