请hrb2008() 兄弟再进来下,帮顶的有分
谢谢对我存储过程的优化，但那个优化有个问题，数据会出来两遍，请费心再改造一下
==========================================================================
CREATE   PROCEDURE   selectAllOpenDocumentsInfo
@user   varchar(50),
@dept   varchar(50),
@lv   varchar(50)  
as
--建临时表整理带 'all '字样的数据与变量到一个集合中
CREATE   TABLE   #FeiliOA_DocumentManage_TypeLookUser(typeID   varchar(50),lookuser   VARCHAR(50))
INSERT   INTO   #FeiliOA_DocumentManage_TypeLookUser
SELECT   typeID,lookuser   FROM   FeiliOA_DocumentManage_TypeLookUser   WHERE   lookuser=@user
UNION   ALL
SELECT   typeID,CASE   WHEN   lookuser= 'ALL '   THEN   @user   END   FROM   FeiliOA_DocumentManage_TypeLookUser
--
CREATE   TABLE   #FeiliOA_DocumentManage_TypeLookDept(typeID   varchar(50),lookdept   VARCHAR(50))
INSERT   INTO   #FeiliOA_DocumentManage_TypeLookDept
SELECT   typeID,lookdept   FROM   FeiliOA_DocumentManage_TypeLookDept   WHERE   lookdept=@dept
UNION   ALL
SELECT   typeID,CASE   WHEN   lookdept= 'ALL '   THEN   @dept   END   FROM   FeiliOA_DocumentManage_TypeLookDept
--
CREATE   TABLE   #FeiliOA_DocumentManage_TypeLookLv(typeID   varchar(50),looklv   VARCHAR(50))
INSERT   INTO   #FeiliOA_DocumentManage_TypeLookLv
SELECT   typeID,looklv   FROM   FeiliOA_DocumentManage_TypeLookLv   WHERE   looklv=@lv
UNION   ALL
SELECT   typeID,CASE   WHEN   looklv= 'ALL '   THEN   @lv   END   FROM   FeiliOA_DocumentManage_TypeLookLv
--将表关联
SELECT   a.documentID,a.documentName,e.typeName,a.documentInfo,a.VesionIDUser,a.vesionId,a.vesionDate,a.documentSize,a.documentSend,a.documenttype,a.documenturl
FROM  
FeiliOA_DocumentManage_DocumentInfo   a   INNER   JOIN   #FeiliOA_DocumentManage_TypeLookUser   b
ON   a.DocumentType=b.typeID   AND   b.lookuser=@user   AND   a.documentStatic= 'a '   INNER   JOIN   FeiliOA_DocumentManage_TypeInfo   e
ON   e.typeid=a.documenttype
UNION   ALL
SELECT   a.documentID,a.documentName,e.typeName,a.documentInfo,a.VesionIDUser,a.vesionId,a.vesionDate,a.documentSize,a.documentSend,a.documenttype,a.documenturl
FROM  
FeiliOA_DocumentManage_DocumentInfo   a   INNER   JOIN
#FeiliOA_DocumentManage_TypeLookDept   c
ON   a.DocumentType=c.typeID   and   c.lookdept=@dept   AND   a.documentStatic= 'a '   INNER   JOIN   FeiliOA_DocumentManage_TypeInfo   e
ON   e.typeid=a.documenttype
UNION   ALL
SELECT   a.documentID,a.documentName,e.typeName,a.documentInfo,a.VesionIDUser,a.vesionId,a.vesionDate,a.documentSize,a.documentSend,a.documenttype,a.documenturl
FROM  
FeiliOA_DocumentManage_DocumentInfo   a   INNER   JOIN
#FeiliOA_DocumentManage_TypeLookLv   d
ON   a.DocumentType=d.typeID   and   d.looklv=@lv   AND   a.documentStatic= 'a '   INNER   JOIN   FeiliOA_DocumentManage_TypeInfo   e
ON   e.typeid=a.documenttype

--删除临时表
DROP   TABLE   #FeiliOA_DocumentManage_TypeLookUser
DROP   TABLE   #FeiliOA_DocumentManage_TypeLookDept
DROP   TABLE   #FeiliOA_DocumentManage_TypeLookLv--此过程没有用group   by
GO


------解决方案--------------------