求一经典语句!存储过程写法。
我的写法不对，请指点：
CREATE PROCEDURE PRO_GetHSLX(@varTbName varchar(50),@varKMBH VARCHAR(100))
AS
BEGIN
SELECT KMBH,KMLB ,BZ1,BZ2 ,BZ3 ,BZ4 ,
BZ5 ,BZ6,ISNULL(KMND, ' ') AS KMND,ISNULL(YELX, ' ') AS YELX
INTO #TB_TEMP
FROM ' +@varTbName + ' WHERE KMBH = ' ' '+@varKMBH ' ' '
GROUP BY KMBH
SELECT A.KMBH AS '编号 ',B.KMMC AS '名称 ',
(CASE WHEN A.KMLB = '1 ' THEN '是 ' ELSE ' ' END) AS '是 ',
(CASE WHEN A.BZ1= '1 ' THEN '是 ' ELSE ' ' END ) AS '单位 ',
(CASE WHEN A.BZ2= '1 ' THEN '是 ' ELSE ' ' END ) AS '个人 ',
(CASE WHEN A.BZ3= '1 ' THEN '是 ' ELSE ' ' END ) AS '现金 ',
(CASE WHEN A.BZ4= '1 ' THEN '是 ' ELSE ' ' END) AS '成本 ' ,
(CASE WHEN A.BZ5= '1 ' THEN '是 ' ELSE ' ' END) AS '产品 ' ,
(CASE WHEN A.BZ6= '1 ' THEN '是 ' ELSE ' ' END) AS '部门 ' ,
(CASE WHEN A.KMND= ' ' THEN ' ' ELSE A.KMND END) AS '专项 ',
(CASE WHEN A.YELX= ' ' THEN ' ' ELSE A.YELX END ) AS '要素 '
FROM #TB_TEMP A, '+@varTbName + ' B
WHERE A.KMBH=B.KMBH ORDER BY A.KMBH
END
[解决办法]
CREATE PROCEDURE PRO_GetHSLX(@varTbName varchar(50),@varKMBH VARCHAR(100))
AS
BEGIN
exec ( '
SELECT KMBH,KMLB ,BZ1,BZ2 ,BZ3 ,BZ4 ,
BZ5 ,BZ6,ISNULL(KMND, ' ' ' ') AS KMND,ISNULL(YELX, ' ' ' ') AS YELX
INTO ##TB_TEMP
FROM ' +@varTbName + ' WHERE KMBH = ' ' '+@varKMBH + ' ' '
GROUP BY KMBH
')
SELECT A.KMBH AS '编号 ',B.KMMC AS '名称 ',
(CASE WHEN A.KMLB = '1 ' THEN '是 ' ELSE ' ' END) AS '是 ',
(CASE WHEN A.BZ1= '1 ' THEN '是 ' ELSE ' ' END ) AS '单位 ',
(CASE WHEN A.BZ2= '1 ' THEN '是 ' ELSE ' ' END ) AS '个人 ',
(CASE WHEN A.BZ3= '1 ' THEN '是 ' ELSE ' ' END ) AS '现金 ',
(CASE WHEN A.BZ4= '1 ' THEN '是 ' ELSE ' ' END) AS '成本 ' ,
(CASE WHEN A.BZ5= '1 ' THEN '是 ' ELSE ' ' END) AS '产品 ' ,
(CASE WHEN A.BZ6= '1 ' THEN '是 ' ELSE ' ' END) AS '部门 ' ,
(CASE WHEN A.KMND= ' ' THEN ' ' ELSE A.KMND END) AS '专项 ',
(CASE WHEN A.YELX= ' ' THEN ' ' ELSE A.YELX END ) AS '要素 '
FROM ##TB_TEMP A, '+@varTbName + ' B
WHERE A.KMBH=B.KMBH ORDER BY A.KMBH
END
[解决办法]
CREATE PROCEDURE PRO_GetHSLX(@varTbName varchar(50),@varKMBH VARCHAR(100))
AS
BEGIN
exec ( '
SELECT KMBH,KMLB ,BZ1,BZ2 ,BZ3 ,BZ4 ,
BZ5 ,BZ6,ISNULL(KMND, ' ' ' ') AS KMND,ISNULL(YELX, ' ' ' ') AS YELX
INTO ##TB_TEMP
FROM ' +@varTbName + ' WHERE KMBH = ' ' '+@varKMBH + ' ' '
GROUP BY KMBH
SELECT A.KMBH AS ' '编号 ' ',B.KMMC AS ' '名称 ' ',
(CASE WHEN A.KMLB = ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '是 ' ',
(CASE WHEN A.BZ1= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END ) AS ' '单位 ' ',
(CASE WHEN A.BZ2= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END ) AS ' '个人 ' ',
(CASE WHEN A.BZ3= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END ) AS ' '现金 ' ',
(CASE WHEN A.BZ4= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '成本 ' ' ,
(CASE WHEN A.BZ5= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '产品 ' ' ,
(CASE WHEN A.BZ6= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '部门 ' ' ,
(CASE WHEN A.KMND= ' ' ' ' THEN ' ' ' ' ELSE A.KMND END) AS ' '专项 ' ',
(CASE WHEN A.YELX= ' ' ' ' THEN ' ' ' ' ELSE A.YELX END ) AS ' '要素 ' '
FROM ##TB_TEMP A, '+@varTbName + ' B
WHERE A.KMBH=B.KMBH ORDER BY A.KMBH
')
END
[解决办法]
CREATE PROCEDURE PRO_GetHSLX(@varTbName varchar(50),@varKMBH VARCHAR(100))
AS
BEGIN
EXEC( '
SELECT KMBH,KMLB ,BZ1,BZ2 ,BZ3 ,BZ4 ,
BZ5 ,BZ6,ISNULL(KMND, ' ' ' ') AS KMND,ISNULL(YELX, ' ' ' ') AS YELX
INTO #TB_TEMP
FROM '+@varTbName+ ' WHERE KMBH = ' ' '+@varKMBH + ' ' '
GROUP BY KMBH
SELECT A.KMBH AS ' '编号 ' ',B.KMMC AS ' '名称 ' ',
(CASE WHEN A.KMLB = ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '是 ' ',
(CASE WHEN A.BZ1= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END ) AS ' '单位 ' ',
(CASE WHEN A.BZ2= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END ) AS ' '个人 ' ',
(CASE WHEN A.BZ3= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END ) AS ' '现金 ' ',
(CASE WHEN A.BZ4= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '成本 ' ' ,
(CASE WHEN A.BZ5= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '产品 ' ' ,
(CASE WHEN A.BZ6= ' '1 ' ' THEN ' '是 ' ' ELSE ' ' ' ' END) AS ' '部门 ' ' ,
(CASE WHEN A.KMND= ' ' ' ' THEN ' ' ' ' ELSE A.KMND END) AS ' '专项 ' ',
(CASE WHEN A.YELX= ' ' ' ' THEN ' ' ' ' ELSE A.YELX END ) AS ' '要素 ' '
FROM #TB_TEMP A, '+@varTbName + ' B
WHERE A.KMBH=B.KMBH ORDER BY A.KMBH
')
END
