几个例子分析sql server自定义函数的性能
sql1：其中dbo.GETUSERROLES(@UserID)) 返回一行，vwRoleOperationRight是一个视图，返回有725行，执行时间有9s
SELECT a.ID, a.ModuleDeployID, a.ModuleDeployCatalogID, a._SeqNo , a.ExistOrgs, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.BrowseExpr = '' THEN 'True' ELSE '(BZRID In [' + a.BrowseExpr + '])' END AS BrowseExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.ModifyExpr = '' THEN 'True' ELSE CASE WHEN a.ModifyExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.ModifyExpr + '])' END END AS ModifyExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AddExpr + ')' AS AddExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.DeleteExpr = '' THEN 'True' ELSE CASE WHEN a.DeleteExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.DeleteExpr + '])' END END AS DeleteExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AuditExpr + ')' AS AuditExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.ReportExpr + ')' AS ReportExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.WithdrawExpr + ')' AS WithdrawExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.DistributeExpr + ')' AS DistributeExpr, a.HasMaster
FROM (
SELECT ror.RoleOperationRightID AS ID, ror.ModuleDeployID AS ModuleDeployID,
ror.ModuleDeployCatalogID AS ModuleDeployCatalogID, ror._SeqNo AS _SeqNo,
ur.ExistOrgs AS ExistOrgs,
CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.BrowseAll = 1 THEN '' ELSE '@SelfUserID' END ELSE CASE WHEN ror.BrowseAll = 1 THEN '' ELSE dbo.GETROLEUSERS(ror.RoleID) END END AS BrowseExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.ModifyAll = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.ModifyAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS ModifyExpr, CASE WHEN ror.[Add] = 1 AND (ur.MyRole = 1 OR NOT ur.[ReadOnly] = 1 AND ror.ModifyAll = 1) THEN 'True' ELSE 'False' END AS AddExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.DeleteAll = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.DeleteAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS DeleteExpr, CASE WHEN ror.Audit = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS AuditExpr, CASE WHEN ror.Report = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS ReportExpr, CASE WHEN ror.WithDraw = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS WithdrawExpr, CASE WHEN ror.Distribute = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS DistributeExpr, ror.IsBill AS HasMaster
FROM vwRoleOperationRight AS ror
JOIN (
SELECT RoleID, [ReadOnly], MyRole, dbo.GETEXISTORGS(RoleID) AS ExistOrgs
FROM dbo.GETUSERROLES(@UserID)) AS ur ON ror.RoleID = ur.RoleID
WHERE ror.ModuleDeployCatalogID IS NOT Null
) AS a
sql2，和上一个sql PDF相比去掉了调用的函数dbo.GETEXISTORGS(RoleID) AS ExistOrgs 以及mmic外层相关的列，执行时间0s
SELECT a.ID, a.ModuleDeployID, a.ModuleDeployCatalogID, a._SeqNo --, a.ExistOrgs, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.BrowseExpr = '' THEN 'True' ELSE '(BZRID In [' + a.BrowseExpr + '])' END AS BrowseExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.ModifyExpr = '' THEN 'True' ELSE CASE WHEN a.ModifyExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.ModifyExpr + '])' END END AS ModifyExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AddExpr + ')' AS AddExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.DeleteExpr = '' THEN 'True' ELSE CASE WHEN a.DeleteExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.DeleteExpr + '])' END END AS DeleteExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AuditExpr + ')' AS AuditExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.ReportExpr + ')' AS ReportExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.WithdrawExpr + ')' AS WithdrawExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.DistributeExpr + ')' AS DistributeExpr, a.HasMaster
FROM (
SELECT ror.RoleOperationRightID AS ID, ror.ModuleDeployID AS ModuleDeployID,
ror.ModuleDeployCatalogID AS ModuleDeployCatalogID, ror._SeqNo AS _SeqNo--,
--ur.ExistOrgs AS ExistOrgs,
--CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.BrowseAll = 1 THEN '' ELSE '@SelfUserID' END ELSE CASE WHEN ror.BrowseAll = 1 THEN '' ELSE dbo.GETROLEUSERS(ror.RoleID) END END AS BrowseExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.ModifyAll = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.ModifyAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS ModifyExpr, CASE WHEN ror.[Add] = 1 AND (ur.MyRole = 1 OR NOT ur.[ReadOnly] = 1 AND ror.ModifyAll = 1) THEN 'True' ELSE 'False' END AS AddExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.DeleteAll = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.DeleteAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS DeleteExpr, CASE WHEN ror.Audit = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS AuditExpr, CASE WHEN ror.Report = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS ReportExpr, CASE WHEN ror.WithDraw = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS WithdrawExpr, CASE WHEN ror.Distribute = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS DistributeExpr, ror.IsBill AS HasMaster
FROM vwRoleOperationRight AS ror
JOIN (
SELECT RoleID, [ReadOnly], MyRole--, dbo.GETEXISTORGS(RoleID) AS ExistOrgs
FROM dbo.GETUSERROLES(@UserID)) AS ur ON ror.RoleID = ur.RoleID
WHERE ror.ModuleDeployCatalogID IS NOT Null
) AS a
第三个sql：
将第一个sql的最里层的sql踢出来做成临时表 速度就快了，不到1s
IF OBJECT_ID('tempdb..#UserRoles') IS NOT NULL DROP TABLE #UserRoles;
SELECT RoleID, [ReadOnly], MyRole, dbo.GETEXISTORGS(RoleID) AS ExistOrgs
INTO #UserRoles
FROM dbo.GETUSERROLES(@UserID)
SELECT a.ID, a.ModuleDeployID, a.ModuleDeployCatalogID, a._SeqNo, a.ExistOrgs, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.BrowseExpr = '' THEN 'True' ELSE '(BZRID In [' + a.BrowseExpr + '])' END AS BrowseExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.ModifyExpr = '' THEN 'True' ELSE CASE WHEN a.ModifyExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.ModifyExpr + '])' END END AS ModifyExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AddExpr + ')' AS AddExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.DeleteExpr = '' THEN 'True' ELSE CASE WHEN a.DeleteExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.DeleteExpr + '])' END END AS DeleteExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AuditExpr + ')' AS AuditExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.ReportExpr + ')' AS ReportExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.WithdrawExpr + ')' AS WithdrawExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.DistributeExpr + ')' AS DistributeExpr, a.HasMaster
FROM (
SELECT ror.RoleOperationRightID AS ID, ror.ModuleDeployID AS ModuleDeployID, ror.ModuleDeployCatalogID AS ModuleDeployCatalogID, ror._SeqNo AS _SeqNo, ur.ExistOrgs AS ExistOrgs, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.BrowseAll = 1 THEN '' ELSE '@SelfUserID' END ELSE CASE WHEN ror.BrowseAll = 1 THEN '' ELSE dbo.GETROLEUSERS(ror.RoleID) END END AS BrowseExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.ModifyAll = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.ModifyAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS ModifyExpr, CASE WHEN ror.[Add] = 1 AND (ur.MyRole = 1 OR NOT ur.[ReadOnly] = 1 AND ror.ModifyAll = 1) THEN 'True' ELSE 'False' END AS AddExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.DeleteAll = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.DeleteAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS DeleteExpr, CASE WHEN ror.Audit = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS AuditExpr, CASE WHEN ror.Report = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS ReportExpr, CASE WHEN ror.WithDraw = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS WithdrawExpr, CASE WHEN ror.Distribute = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS DistributeExpr, ror.IsBill AS HasMaster
FROM vwRoleOperationRight AS ror
JOIN #UserRoles AS ur ON ror.RoleID = ur.RoleID
WHERE ror.ModuleDeployCatalogID IS NOT Null
) AS a
SELECT a.ID, a.ModuleDeployID, a.ModuleDeployCatalogID, a._SeqNo , a.ExistOrgs, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.BrowseExpr = '' THEN 'True' ELSE '(BZRID In [' + a.BrowseExpr + '])' END AS BrowseExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.ModifyExpr = '' THEN 'True' ELSE CASE WHEN a.ModifyExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.ModifyExpr + '])' END END AS ModifyExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AddExpr + ')' AS AddExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.DeleteExpr = '' THEN 'True' ELSE CASE WHEN a.DeleteExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.DeleteExpr + '])' END END AS DeleteExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AuditExpr + ')' AS AuditExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.ReportExpr + ')' AS ReportExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.WithdrawExpr + ')' AS WithdrawExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.DistributeExpr + ')' AS DistributeExpr, a.HasMaster
FROM (
SELECT ror.RoleOperationRightID AS ID, ror.ModuleDeployID AS ModuleDeployID,
ror.ModuleDeployCatalogID AS ModuleDeployCatalogID, ror._SeqNo AS _SeqNo,
ur.ExistOrgs AS ExistOrgs,
CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.BrowseAll = 1 THEN '' ELSE '@SelfUserID' END ELSE CASE WHEN ror.BrowseAll = 1 THEN '' ELSE dbo.GETROLEUSERS(ror.RoleID) END END AS BrowseExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.ModifyAll = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.ModifyAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS ModifyExpr, CASE WHEN ror.[Add] = 1 AND (ur.MyRole = 1 OR NOT ur.[ReadOnly] = 1 AND ror.ModifyAll = 1) THEN 'True' ELSE 'False' END AS AddExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.DeleteAll = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.DeleteAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS DeleteExpr, CASE WHEN ror.Audit = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS AuditExpr, CASE WHEN ror.Report = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS ReportExpr, CASE WHEN ror.WithDraw = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS WithdrawExpr, CASE WHEN ror.Distribute = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS DistributeExpr, ror.IsBill AS HasMaster
FROM vwRoleOperationRight AS ror
JOIN (
SELECT RoleID, [ReadOnly], MyRole, dbo.GETEXISTORGS(RoleID) AS ExistOrgs
FROM dbo.GETUSERROLES(@UserID)) AS ur ON ror.RoleID = ur.RoleID
WHERE ror.ModuleDeployCatalogID IS NOT Null
) AS a
sql2，和上一个sql PDF相比去掉了调用的函数dbo.GETEXISTORGS(RoleID) AS ExistOrgs 以及mmic外层相关的列，执行时间0s
SELECT a.ID, a.ModuleDeployID, a.ModuleDeployCatalogID, a._SeqNo --, a.ExistOrgs, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.BrowseExpr = '' THEN 'True' ELSE '(BZRID In [' + a.BrowseExpr + '])' END AS BrowseExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.ModifyExpr = '' THEN 'True' ELSE CASE WHEN a.ModifyExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.ModifyExpr + '])' END END AS ModifyExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AddExpr + ')' AS AddExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.DeleteExpr = '' THEN 'True' ELSE CASE WHEN a.DeleteExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.DeleteExpr + '])' END END AS DeleteExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AuditExpr + ')' AS AuditExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.ReportExpr + ')' AS ReportExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.WithdrawExpr + ')' AS WithdrawExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.DistributeExpr + ')' AS DistributeExpr, a.HasMaster
FROM (
SELECT ror.RoleOperationRightID AS ID, ror.ModuleDeployID AS ModuleDeployID,
ror.ModuleDeployCatalogID AS ModuleDeployCatalogID, ror._SeqNo AS _SeqNo--,
--ur.ExistOrgs AS ExistOrgs,
--CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.BrowseAll = 1 THEN '' ELSE '@SelfUserID' END ELSE CASE WHEN ror.BrowseAll = 1 THEN '' ELSE dbo.GETROLEUSERS(ror.RoleID) END END AS BrowseExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.ModifyAll = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.ModifyAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS ModifyExpr, CASE WHEN ror.[Add] = 1 AND (ur.MyRole = 1 OR NOT ur.[ReadOnly] = 1 AND ror.ModifyAll = 1) THEN 'True' ELSE 'False' END AS AddExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.DeleteAll = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.DeleteAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS DeleteExpr, CASE WHEN ror.Audit = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS AuditExpr, CASE WHEN ror.Report = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS ReportExpr, CASE WHEN ror.WithDraw = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS WithdrawExpr, CASE WHEN ror.Distribute = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS DistributeExpr, ror.IsBill AS HasMaster
FROM vwRoleOperationRight AS ror
JOIN (
SELECT RoleID, [ReadOnly], MyRole--, dbo.GETEXISTORGS(RoleID) AS ExistOrgs
FROM dbo.GETUSERROLES(@UserID)) AS ur ON ror.RoleID = ur.RoleID
WHERE ror.ModuleDeployCatalogID IS NOT Null
) AS a
第三个sql：
将第一个sql的最里层的sql踢出来做成临时表 速度就快了，不到1s
IF OBJECT_ID('tempdb..#UserRoles') IS NOT NULL DROP TABLE #UserRoles;
SELECT RoleID, [ReadOnly], MyRole, dbo.GETEXISTORGS(RoleID) AS ExistOrgs
INTO #UserRoles
FROM dbo.GETUSERROLES(@UserID)
SELECT a.ID, a.ModuleDeployID, a.ModuleDeployCatalogID, a._SeqNo, a.ExistOrgs, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.BrowseExpr = '' THEN 'True' ELSE '(BZRID In [' + a.BrowseExpr + '])' END AS BrowseExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.ModifyExpr = '' THEN 'True' ELSE CASE WHEN a.ModifyExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.ModifyExpr + '])' END END AS ModifyExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AddExpr + ')' AS AddExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and ' + CASE WHEN a.DeleteExpr = '' THEN 'True' ELSE CASE WHEN a.DeleteExpr = '-1' THEN 'False' ELSE '(BZRID In [' + a.DeleteExpr + '])' END END AS DeleteExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.AuditExpr + ')' AS AuditExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.ReportExpr + ')' AS ReportExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.WithdrawExpr + ')' AS WithdrawExpr, '(@SelfOrgID in [' + a.ExistOrgs + ']) and (' + a.DistributeExpr + ')' AS DistributeExpr, a.HasMaster
FROM (
SELECT ror.RoleOperationRightID AS ID, ror.ModuleDeployID AS ModuleDeployID, ror.ModuleDeployCatalogID AS ModuleDeployCatalogID, ror._SeqNo AS _SeqNo, ur.ExistOrgs AS ExistOrgs, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.BrowseAll = 1 THEN '' ELSE '@SelfUserID' END ELSE CASE WHEN ror.BrowseAll = 1 THEN '' ELSE dbo.GETROLEUSERS(ror.RoleID) END END AS BrowseExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.ModifyAll = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.ModifyAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.ModifySelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS ModifyExpr, CASE WHEN ror.[Add] = 1 AND (ur.MyRole = 1 OR NOT ur.[ReadOnly] = 1 AND ror.ModifyAll = 1) THEN 'True' ELSE 'False' END AS AddExpr, CASE WHEN ur.MyRole = 1 THEN CASE WHEN ror.DeleteAll = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 THEN '@SelfUserID' ELSE '-1' END END ELSE CASE WHEN ror.DeleteAll = 1 AND NOT ur.[ReadOnly] = 1 THEN '' ELSE CASE WHEN ror.DeleteSelf = 1 AND NOT ur.[ReadOnly] = 1 THEN dbo.GETROLEUSERS(ror.RoleID) ELSE '-1' END END END AS DeleteExpr, CASE WHEN ror.Audit = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS AuditExpr, CASE WHEN ror.Report = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS ReportExpr, CASE WHEN ror.WithDraw = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS WithdrawExpr, CASE WHEN ror.Distribute = 1 AND NOT ur.[ReadOnly] = 1 THEN 'True' ELSE 'False' END AS DistributeExpr, ror.IsBill AS HasMaster
FROM vwRoleOperationRight AS ror
JOIN #UserRoles AS ur ON ror.RoleID = ur.RoleID
WHERE ror.ModuleDeployCatalogID IS NOT Null
) AS a
