日期:2014-05-18  浏览次数:20452 次

求一经典语句!存储过程写法。
我的写法不对,请指点:
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 &