请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
[解决办法]
表的名字真長 ^@^
~~~原貼地址是哪啊
------解决方案--------------------
帮你顶一下。哈哈
[解决办法]
搬张板凳来UP
[解决办法]
頂了.
[解决办法]
路过帮顶
[解决办法]
不顶寻常贴~
[解决办法]
呵呵，先把其中的union all替成union试一下。中午的时候我再来看一下，有没有其它问题
[解决办法]
帮顶：）
