我们如何在表变量上创建非聚集索引？
创建表@risk
(rskid int)
在 @risk(rskid) 上创建非聚集索引 r_rskid_nc
这是行不通的？？
我的程序
我该如何优化它？？
更改程序 [dbo].[SPR_LV004]
(
@TopN INT
,@LoggedUserId INT
,@Entity VARCHAR(255)
,@OpModel VARCHAR(255)
,@RiskCat VARCHAR(255)
,@RsdlInh VARCHAR(1)
,@DisplayAction VARCHAR(1)
,@LastAssDate 日期时间
)
作为
SET NOCOUNT ON
DECLARE @Thisdate DATETIME
SET @ThisDate = GETDATE()
DECLARE @MainFilter TABLE(
fcaName VARCHAR(100)
,fctName VARCHAR(100)
,rskId INT PRIMARY KEY CLUSTERED )
DECLARE @Tgt TABLE(rasRiskId INT
,rasId INT
,RAMName VARCHAR(100)
,RAMColour INT
,RAMImpDesc VARCHAR(100)
,RAMLikDesc VARCHAR(100)
,RAMImpScore INT
,RAMLikScore INT
,LastScore INT
,AnnualExposure NUMERIC(15, 0)
,Currency VARCHAR(9)
,OverallExp NUMERIC(15,0)
,Frequency INT
,LastAssessmentDate DATETIME)
DECLARE @Inh TABLE(rasRiskId INT
,rasId INT
,RAMName VARCHAR(100)
,RAMColour INT
,RAMImpDesc VARCHAR(100)
,RAMLikDesc VARCHAR(100)
,RAMImpScore INT
,RAMLikScore INT
,LastScore INT
,AnnualExposure NUMERIC(15, 0)
,Currency VARCHAR(9)
,OverallExp NUMERIC(15,0)
,Frequency INT
,LastAssessmentDate DATETIME)
DECLARE @Res TABLE(rasRiskId INT
,rasId INT
,RAMName VARCHAR(100)
,RAMColour INT
,RAMimpDesc VARCHAR(100)
,RAMlikDesc VARCHAR(100)
,RAMImpScore INT
,RAMLikScore INT
,LastScore INT
,AnnualExposure NUMERIC(15, 0)
,Currency VARCHAR(9)
,OverallExp NUMERIC(15,0)
,Frequency INT
,LastAssessmentDate DATETIME)
DECLARE @RiskData TABLE(RAMScore INT
,AnnExp NUMERIC(15, 0)
,rskId INT
,RiskID VARCHAR(20)
,rskDescription VARCHAR(150)
,LongDesc VARCHAR(4000)
,RiskAssessmentDate VARCHAR(100)
,RAMName VARCHAR(100)
,rskRAMId INT
,rskRiskProfile INT
,EntityInfo VARCHAR(100)
,OwnerName VARCHAR(100)
,NomineeName VARCHAR(100)
,ReviewerName VARCHAR(100)
,TgtRasId INT
,TgtRamName VARCHAR(100)
,TgtRamColour INT
,TgtRamImpDesc VARCHAR(100)
,TgtRamLikDesc VARCHAR(100)
,TgtRamImpScore INT
,TgtRamLikScore INT
,TgtLastScore INT
,TgtAnnualExp Numeric(15,0)
,TgtCurrency VARCHAR(9)
,InhRasId INT
,InhRamName VARCHAR(100)
,InhRamColour INT
,InhRamImpDesc VARCHAR(100)
,InhRamLikDesc VARCHAR(100)
,InhRamImpScore INT
,InhRamLikScore INT
,InhLastScore INT
,InhAnnualExp Numeric(15,0)
,InhCurrency VARCHAR(9)
,RsdRasId INT
,RsdRamName VARCHAR(100)
,RsdRamColour INT
,RsdRamImpDesc VARCHAR(100)
,RsdRamLikDesc VARCHAR(100)
,RsdRamImpScore INT
,RsdRamLikScore INT
,RsdLastScore INT
,rsdAnnualExp Numeric(15,0)
,RsdCurrency VARCHAR(9)
,fcaName VARCHAR(100)
,fctName VARCHAR(100)
,CCRId VARCHAR(15)
,crrDescription VARCHAR(150)
,Assessed char(1)
,Attested Char(1)
,DisplayAction VARCHAR(1)
,opModelName VARCHAR(255)
,severity NUMERIC(15,0)
,topriskcat VARCHAR(100)
,TgtOverallExp NUMERIC(15,0)
,TgtFrequency INT
,InhOverallExp NUMERIC(15,0)
,InhFrequency INT
,ResOverallExp NUMERIC(15,0)
,ResFrequency INT)
DECLARE @RiskCtrl TABLE(
rcnRiskId INT
,rcnId INT
,ControlInfo VARCHAR(1100)
,Performance INT --VARCHAR(60) ,Design INT --VARCHAR(60)
,ControlOwner VARCHAR(100)
,ControlNominee VARCHAR(100)
,ControlReviewer VARCHAR(100))
DECLARE @ACTIONS TABLE(
ActionRiskId INT
,ControlID INT
,actTgtCompleteDate VARCHAR(9)
,actTgtODueDate VARCHAR(9)
,RATgtDate VARCHAR(9)
,ActNominee VARCHAR(100)
,ActOwner VARCHAR(100)
,ActCompleteAmt INT
,ActionDetail VARCHAR(MAX)
,AType INT
,ActionState VARCHAR(90) )
DECLARE @TopRiskSort TABLE(
RdRecId INT IDENTITY(1,1) NOT NULL
,AnnExp NUMERIC(15, 0)
,rskId INT
,severity NUMERIC(15,0)
)
DECLARE @TopRisk TABLE(
AnnExp NUMERIC(15, 0)
,rskId INT
,severity NUMERIC(15,0)
)
-- New tables for LV004 06-JAN-2010
-- gets the parent Operational Model -- by priyanka
DECLARE @OpModels TABLE(
OpModelName VARCHAR(255),
rskId INT)
DECLARE @TopRiskCat TABLE(
rskid INT PRIMARY KEY CLUSTERED,
topRiskCat VARCHAR(1000))
--LOCAL VARIABLES
DECLARE @Query VARCHAR(8000)
--Create filter data in a Table datatype
INSERT INTO @MainFilter
SELECT DISTINCT
NULL as fcaName
,NULL as fctName
,R.rskId
FROM RiskProfiles RP
INNER JOIN RiskProfileFactAnal RPF
ON RP.rwkReference = RPF.wfaRiskProfile
INNER JOIN FactCatAnal FA
ON RPF.wfaFactAnalId = FA.fcaId
INNER JOIN UserViewsEntity UE
ON RPF.wfaRiskProfile = UE.uveEntId
INNER JOIN Risks R
ON RP.rwkReference = R.rskRiskProfile
INNER JOIN RiskAnalysis RA
ON R.rskId = RA.ranRiskId
INNER JOIN RiskCategory RC
ON RA.ranRiskAnalId = RC.rctId
LEFT OUTER JOIN RiskAssessment RASS
ON R.rskId = RASS.rasRiskId
AND R.rskRelevant = 'Y'
WHERE (UE.uveUserId = @LoggedUserId)
--AND FA.fcaId in (SELECT FinalID FROM @FinalTree)
AND FA.fcaId in (SELECT TreeTableID FROM [dbo].[OpModelListToTable](@OpModel)) AND RP.rwkReference in (SELECT TreeTableID FROM [dbo].[EntityListToTable](@Entity))
AND RC.rctid in (SELECT TreeTableID FROM [dbo].[RiskCategoryListToTable](@RiskCat))
AND R.rskOpendate <= @LastAssDate
-- Insertion into new tables
INSERT INTO @OpModels
SELECT FCA.fcaName AS TopLevelBusinessLine,
Rsk.rskid
FROM @MainFilter MF
INNER JOIN Risks RSK
ON RSK.rskid= MF.rskId
INNER JOIN RiskProfileFactAnal RPF
ON RPF.wfaRISkProfile = RSK.rskRiskProfile
INNER JOIN FactCatAnal FCA
ON FCA.fcaId = RPF.wfaFactAnalId
INNER JOIN FactCategory FC
ON FC.fctid = FCA.fcaCategory and FC.fctName='Business Unit'
INSERT INTO @TopRiskCat
SELECT R.rskid
,dbo.fn_GetTopParentRiskCategoryScalar(RC.rctId) as TopLevelRiskCat
FROM @MainFilter MF
INNER JOIN RISKS R
ON R.rskid= MF.rskId
INNER JOIN RiskAnalysis RA
ON R.rskId = RA.ranRiskId
INNER JOIN RiskCategory as RC
ON RA.ranRiskAnalId = RC.rctId
-- Target Data
INSERT INTO @Tgt
SELECT TgtRskID,rasId,ramaltName,ramaltColour,TgtRAMDescr,TgtRAMLikDescr,TgtRAMImpScr,TgtRAMLikScr
,LastScore , AnnualExposure,curCode, TgtOverallExp,TgtFrequency,RiskLastAssessment FROM (
SELECT
RA.rasRiskId AS TgtRskID
,RA.rasId
,RM.ramaltName
,RM.ramaltColour
,isnull(RI.ramimpDescription,'') AS TgtRAMDescr
,isnull(RL.ramlikDescription,'') AS TgtRAMLikDescr
,isnull(RA.rasRAMImpactScore,0) AS TgtRAMImpScr
,isnull(RA.rasRAMLikelihoodScore,0) AS TgtRAMLikScr
,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore
,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
,RCU.curCode
,RA.rasExposure as TgtOverallExp
,RA.rasRAMLikFreqEntered as TgtFrequency
,RA.rasAssessmentDate as RiskLastAssessment
FROM RAMImpacts RI
INNER JOIN RiskAssessment RA
ON RI.ramimpScore = RA.rasRAMImpactScore
AND RI.ramimpRAMId = RA.rasRAMId
INNER JOIN Risks R
ON R.rskId = RA.rasRiskId
INNER JOIN @MainFilter MF
ON MF.rskId = R.rskId
INNER JOIN RAMALTs RM
ON RA.rasRAMId = RM.ramaltRAMId
AND RA.rasRAMALTAmount = RM.ramaltAmount
INNER JOIN RAMLikelihoods RL
ON RA.rasRAMLikelihoodScore = RL.ramlikScore
AND RA.rasRAMId = RL.ramlikRAMId
LEFT OUTER JOIN RAM RMS
ON RA.rasRAMId = RMS.ramId
LEFT OUTER JOIN Currencies RCU
ON RMS.ramCurrencyId = RCU.curId
WHERE (RA.rasType = 'T') )
A --WHERE A.RiskLastAssessment IN
INNER JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM RiskAssessment RAouter
--WHERE RAouter.rasAssessmentDate <= @LastAssDate
Group by RAouter.rasRiskid) B
ON A.RiskLastAssessment = B.rasAssessmentDate and A.TgtRskID = B.rasRiskid
--Inherent data
INSERT INTO @Inh
SELECT InhRskID,rasId,ramaltName,ramaltColour,InhRAMDescr,InhRAMLikDescr,InhRAMImpScr,InhRAMLikScr,LastScore,AnnualExposure,curCode,
InhOverallExp,InhFrequency,RiskLastAssessment FROM (
SELECT RA.rasRiskId AS InhRskID
,RA.rasId
,RM.ramaltName
,RM.ramaltColour
,isnull(RI.ramimpDescription,'') AS InhRAMDescr
,isnull(RL.ramlikDescription,'') AS InhRAMLikDescr
,isnull(RA.rasRAMImpactScore,0) AS InhRAMImpScr
,isnull(RA.rasRAMLikelihoodScore,0) AS InhRAMLikScr
,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore
,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
,RCU.curCode
,RA.rasExposure as InhOverallExp
,RA.rasRAMLikFreqEntered as InhFrequency
,RA.rasAssessmentDate as RiskLastAssessment
FROM RAMImpacts RI
INNER JOIN RiskAssessment RA
ON RI.ramimpScore = RA.rasRAMImpactScore
AND RI.ramimpRAMId = RA.rasRAMId
INNER JOIN Risks R
ON R.rskId = RA.rasRiskId
INNER JOIN @MainFilter MF
ON MF.rskId = R.rskId
INNER JOIN RAMALTs RM
ON RA.rasRAMId = RM.ramaltRAMId
AND RA.rasRAMALTAmount = RM.ramaltAmount
INNER JOIN RAMLikelihoods RL
ON RA.rasRAMLikelihoodScore = RL.ramlikScore
AND RA.rasRAMId = RL.ramlikRAMId
LEFT OUTER JOIN RAM RMS
ON RA.rasRAMId = RMS.ramId
LEFT OUTER JOIN Currencies RCU
ON RMS.ramCurrencyId = RCU.curId
WHERE (RA.rasType = 'I'))-- AND (RA.rasIsLatest = 'Y')
A --WHERE A.RiskLastAssessment IN
INNER JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM RiskAssessment RAouter
--WHERE RAouter.rasAssessmentDate <= @LastAssDate
Group by RAouter.rasRiskid) B
ON A.RiskLastAssessment = B.rasAssessmentDate and A.InhRskID = B.rasRiskid
--Residual data
INSERT INTO @Res
SELECT ResRskID,rasId,ramaltName,ramaltColour,ResRAMDescr,ResRAMLikDescr,ResRAMImpScr,ResRAMLikScr,LastScore,AnnualExposure,curCode,
ResOverallExp,ResFrequency,RiskLastAssessment FROM(
SELECT RA.rasRiskId AS ResRskID
,RA.rasId
,RM.ramaltName
,RM.ramaltColour
,isnull(RI.ramimpDescription,'') AS ResRAMDescr
,isnull(RL.ramlikDescription,'') AS ResRAMLikDescr
,isnull(RA.rasRAMImpactScore,0) AS ResRAMImpScr
,isnull(RA.rasRAMLikelihoodScore,0) AS ResRAMLikScr
,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'R'),0) AS LastScore
,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
,RCU.curCode
,RA.rasExposure as ResOverallExp
,RA.rasRAMLikFreqEntered as ResFrequency
,RA.rasAssessmentDate as RiskLastAssessment
FROM RAMImpacts RI
INNER JOIN RiskAssessment RA
ON RI.ramimpScore = RA.rasRAMImpactScore
AND RI.ramimpRAMId = RA.rasRAMId
INNER JOIN Risks R
ON R.rskId = RA.rasRiskId
INNER JOIN @MainFilter MF
ON MF.rskId = R.rskId
INNER JOIN RAMALTs RM
ON RA.rasRAMId = RM.ramaltRAMId
AND RA.rasRAMALTAmount = RM.ramaltAmount
INNER JOIN RAMLikelihoods RL
ON RA.rasRAMLikelihoodScore = RL.ramlikScore
AND RA.rasRAMId = RL.ramlikRAMId
LEFT OUTER JOIN RAM RMS
ON RA.rasRAMId = RMS.ramId
LEFT OUTER JOIN Currencies RCU
ON RMS.ramCurrencyId = RCU.curId
WHERE (RA.rasType = 'R'))-- AND (RA.rasIsLatest = 'Y')
A --WHERE A.RiskLastAssessment IN
INNER JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM RiskAssessment RAouter
--WHERE RAouter.rasAssessmentDate <= @LastAssDate
Group by RAouter.rasRiskid) B
ON A.RiskLastAssessment = B.rasAssessmentDate and A.ResRskID = B.rasRiskid
-- Risk Data to display on report
INSERT INTO @RiskData
SELECT RA.rasRAMImpactScore * RA.rasRAMLikelihoodScore AS RAMScore
,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnExp -- Required for sorting.
,RSK.rskId
,dbo.RiskPrefixId(RSK.rskId) AS RiskID
,isnull(RSK.rskDescription,'')
,isnull(SUBSTRING(RSK.rskLongDesc, 1, 4000),'') AS LongDesc
,CASE WHEN YEAR(RSK.rskAssNext) <= 1900
THEN NULL
ELSE CONVERT(VARCHAR(9), RSK.rskAssNext, 6)
END as RiskAssessmentDate
,RAM.RAMName
,RSK.rskRAMId
,RSK.rskRiskProfile
,RP.rwkDesc + ': ' + dbo.EntityPrefixId(RSK.rskRiskProfile) AS EntityInfo
,isnull(dbo.FULLNAME(RSK.rskOwner),'...') AS OwnerName
,isnull(dbo.FULLNAME(RSK.rskNomineeId),'...') AS NomineeName
,isnull(dbo.FULLNAME(RSK.rskReviewer),'...') AS ReviewerName
,T.rasId AS TgtRasId
,T.RAMName AS TgtRamName
,T.RAMColour AS TgtRamColour
,T.RAMImpDesc AS TgtRamImpDesc
,T.RAMLikDesc AS TgtRamLikDesc
,isnull(T.RAMImpScore,0) AS TgtRamImpScore
,isnull(T.RAMLikScore,0) AS TgtRamLikScore
,isnull(T.LastScore,0) AS TgtLastScore
,isnull(T.AnnualExposure,0.00) AS TgtAnnualExp
,T.Currency as TgtCurrency
,I.rasId AS InhRasId
,I.RAMName AS InhRamName
,I.RAMColour AS InhRamColour
,I.RAMImpDesc AS InhRamImpDesc
,I.RAMLikDesc AS InhRamLikDesc
,isnull(I.RAMImpScore,0) AS InhRamImpScore
,isnull(I.RAMLikScore,0) AS InhRamLikScore
,isnull(I.LastScore,0) AS InhLastScore
,isnull(I.AnnualExposure,0.00) AS InhAnnualExp
,I.Currency as InhCurrency
,R.rasId AS RsdRasId
,R.RAMName AS RsdRamName
,R.RAMColour AS RsdRamColour
,R.RAMimpDesc AS RsdRamImpDesc
,R.RAMlikDesc AS RsdRamLikDesc
,isnull(R.RAMImpScore,0) AS RsdRamImpScore
,isnull(R.RAMLikScore,0) AS RsdRamLikScore
,isnull(R.LastScore,0) AS RsdLastScore
,isnull(R.AnnualExposure,0.00) AS RsdAnnualExp
,R.Currency as RsdCurrency
,MF.fcaName
,MF.fctName
,dbo.CRRPrefixId(CRR.CrrId) AS CCRId
,isnull(CRR.crrDescription,'')
,CASE WHEN ISNULL(RA.rasid,0) = 0 THEN 'N' Else 'Y' END AS Assessed
,CASE WHEN UPPER((RA.rasstatus)) = 'A' THEN 'Y' ELSE 'N' END AS Attested
,@DisplayAction
,OP.OpModelName as TopLevelOpModel
,RSK.rskSeverity as RiskSeverity
,TRC.topRiskCat as TopLevelRiskCat
,Isnull(T.OverallExp/1000,0) as TgtOverallExp
,Isnull(T.Frequency,0) as TgtFrequency
,IsNUll(I.OverallExp/1000,0) as InhOverallExp
,ISNUll(I.Frequency,0) as InhFrequency
,IsNUll(R.OverallExp/1000,0) as ResOverallExp
,Isnull(R.Frequency,0) as ResFrequency
FROM Risks RSK
INNER JOIN @MainFilter MF
ON MF.rskId = RSK.rskId
INNER JOIN RiskProfiles RP
ON RSK.rskRiskProfile = RP.rwkReference
INNER JOIN RAM
ON RP.rwkRAMID = RAM.ramId
LEFT OUTER JOIN CentralRiskRegister CRR
ON CRR.crrId = RSK.rskCRRId -- Added on 03/Jan/08
LEFT OUTER JOIN RiskAssessment RA
ON RSK.rskId = RA.rasRiskId
AND RA.rasIsLatest = 'Y'
AND RA.rasType = CASE WHEN @RsdlInh = '1' THEN 'R'
WHEN @RsdlInh = '2' THEN 'I'
ELSE 'T'
END
LEFT OUTER JOIN @Tgt T
ON RSK.rskId = T.rasRiskId
LEFT OUTER JOIN @Inh I
ON RSK.rskId = I.rasRiskId
LEFT OUTER JOIN @Res R
ON RSK.rskId = R.rasRiskId
LEFT OUTER JOIN @OpModels OP
ON RSK.rskId = OP.rskid
LEFT OUTER JOIN @TopRiskCat TRC
ON RSk.rskid = TRC.rskid
WHERE RSK.rskRelevant = 'Y'
-- Get Risk data in descending order of Severity
INSERT INTO @TopRiskSort
select distinct AnnExp,rskId,severity
FROM @RiskData
Order BY Severity desc;
--Order BY AnnExp desc;
--If @TopN values is 0 take all the recods
IF @TopN = 0
BEGIN
INSERT INTO @TopRisk
select AnnExp
,rskId
,severity
from @TopRiskSort
END
ELSE
BEGIN -- Else
INSERT INTO @TopRisk
select AnnExp
,rskId
,severity
from @TopRiskSort
where RdRecId <= @topn;
END
--Controls for the above Risks
INSERT INTO @RiskCtrl
SELECT RD.rskId
,RC.rcnId
,dbo.RiskControlPrefixId(RC.rcnId) + ':' + rcnShortDescr AS ControlInfo
,(SELECT efid FROM Effectiveness WHERE efId = CA.rcaEffectiveness) AS Performance --ACCR-1327
,(SELECT adid FROM Adequacy WHERE adId = CA.rcaAdequacy) AS Design ,dbo.FULLNAME(RC.rcnOwnerId) AS ControlOwner
,dbo.FULLNAME(RC.rcnNomineeId) AS ControlNominee
,dbo.FULLNAME(RC.rcnReviewerId) AS ControlReviewer
FROM @TopRisk TR
LEFT OUTER JOIN @RiskData RD ON RD.rskId=TR.rskId
LEFT OUTER JOIN RiskControls RC ON RC.rcnRiskId = RD.rskId
LEFT OUTER JOIN RiskCtrlAss CA ON RC.rcnId = CA.rcaRiskCtrlId
WHERE (ISNULL(CA.rcaId, 0) = (SELECT IsNull(MAX(RA.rcaId), 0) FROM RiskCtrlAss RA
WHERE RA.rcaRiskCtrlId = RC.rcnId))
--Risk and Risk Controls Actions
INSERT INTO @ACTIONS
-- Risk Actions fro all the risks in @RiskData table
SELECT A.actOwningId AS ActionRiskId
,NULL AS ControlID
,CASE WHEN (YEAR(A.actTargetDate) > 1900)
AND (
A.actState = 4
)
THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
ELSE NULL
END
AS actTgtCompleteDate
,CASE WHEN (YEAR(A.actTargetDate) > 1900)
AND (A.actState < 3)
AND (A.actTargetDate <= @ThisDate)
THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
ELSE NULL
END AS actTgtODueDate
,CASE WHEN (YEAR(A.actTargetDate) > 1900)
AND (A.actState < 3)
AND (A.actTargetDate <= @ThisDate)
THEN NULL --'overdue'
WHEN YEAR(A.actTargetDate) > 1900
AND (
A.actState = 3
OR A.actState = 4
) THEN NULL --'complete'
WHEN YEAR(A.actTargetDate) <= 1900
THEN NULL
ELSE CONVERT(VARCHAR(9), A.actTargetDate, 6)
END AS RATgtDate
,dbo.FULLNAME(A.actNomineeId) AS ActNominee
,dbo.FULLNAME(A.actOwnerId) AS ActOwner
,A.actCompletionAmount AS CompletionAmount
,isnull((CASE A.actOwningTableNum
WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId)+ '/' + dbo.ActionPrefixId(A.actId)+ ':' + Cast(actCommentary as Varchar(MAX))
WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END),'') AS ActionDetail
,actOwningTableNum AS AType
,dbo.[ActionStatusStr](A.actState)
FROM Actions A
INNER JOIN @RiskData RD ON RD.rskId = A.actOwningId
INNER JOIN @TopRisk TR ON TR.rskId = A.actOwningId
WHERE A.actOwningTableNum = 3
AND (A.actState < 4) AND (A.actstate != 3)
UNION
-- Risk Control Actions for all the risk controls in @RiskCtrl table
SELECT RC.rcnRiskId AS ActionRiskId
,RC.rcnId AS ControlID
,CASE WHEN (YEAR(A.actTargetDate) > 1900)
AND (
-- A.actState > 2 AND
A.actState <= 4
)
THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
ELSE NULL
END
AS actTgtCompleteDate
,CASE WHEN (YEAR(A.actTargetDate) > 1900)
AND (A.actState < 3)
AND (A.actTargetDate <= @ThisDate)
THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
ELSE NULL
END AS actTgtCompleteDate
,CASE WHEN (YEAR(A.actTargetDate) > 1900)
AND (A.actState < 3)
AND (A.actTargetDate <= @ThisDate)
THEN NULL --'overdue'
WHEN YEAR(A.actTargetDate) > 1900
AND (
A.actState = 3
OR A.actState = 4
) THEN NULL --'compete'
WHEN YEAR(A.actTargetDate) <= 1900
THEN NULL
ELSE CONVERT (VARCHAR(9), A.actTargetDate, 6)
END AS RATgtDate
,dbo.FULLNAME(actNomineeId) AS ActNominee
,dbo.FULLNAME(actOwnerId) AS ActOwner
,A.actCompletionAmount AS CompletionAmount
,CASE A.actOwningTableNum
WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId) + '/' + dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX))
WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END AS ActionDetail
-- A.actDescription END AS ActionDetail ,actOwningTableNum AS AType
,dbo.[ActionStatusStr](A.actState)
FROM Actions A
RIGHT OUTER JOIN RiskControls RC
ON A.actOwningId = RC.rcnId
INNER JOIN @RiskCtrl TRC
ON TRC.rcnRiskId = RC.rcnRiskId
WHERE (A.actOwningTableNum = 2)
AND (A.actState < 4) AND (A.actstate != 3)
IF (@RsdlInh = '1')
BEGIN
SELECT RD.*
,NULL AS rcnId
,NULL AS ControlInfo
,NULL AS Performance
,NULL AS Design
,NULL AS ControlOwner
,NULL AS ControlNominee
,NULL AS ControlReviewer
,A.*
FROM @RiskData RD
INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
LEFT OUTER JOIN @ACTIONS A
ON (
RD.rskid = A.ActionRiskId
AND A.AType = 3
)
UNION
SELECT RD.*
,RC.rcnId
,RC.ControlInfo
,RC.Performance
,RC.Design
,RC.ControlOwner
,RC.ControlNominee
,RC.ControlReviewer
,A.*
FROM @RiskData RD
INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
LEFT OUTER JOIN @ACTIONS A
ON (
A.AType = 2
AND RC.rcnId = A.ControlID
)
ORDER BY RD.RsdAnnualExp DESC
END
IF (@RsdlInh = '2')
BEGIN
SELECT RD.*
,NULL AS rcnId
,NULL AS ControlInfo
,NULL AS Performance
,NULL AS Design
,NULL AS ControlOwner
,NULL AS ControlNominee
,NULL AS ControlReviewer
,A.*
FROM @RiskData RD
INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
LEFT OUTER JOIN @ACTIONS A
ON (
RD.rskid = A.ActionRiskId
AND A.AType = 3
)
UNION
SELECT RD.*
,RC.rcnId
,RC.ControlInfo
,RC.Performance
,RC.Design
,RC.ControlOwner
,RC.ControlNominee
,RC.ControlReviewer
,A.*
FROM @RiskData RD
INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
LEFT OUTER JOIN @ACTIONS A
ON (
A.AType = 2
AND RC.rcnId = A.ControlID
)
ORDER BY RD.InhAnnualExp DESC
END
IF (@RsdlInh = '3')
BEGIN
SELECT RD.*
,NULL AS rcnId
,NULL AS ControlInfo
,NULL AS Performance
,NULL AS Design
,NULL AS ControlOwner
,NULL AS ControlNominee
,NULL AS ControlReviewer
,A.*
FROM @RiskData RD
INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
LEFT OUTER JOIN @ACTIONS A
ON (
RD.rskid = A.ActionRiskId
AND A.AType = 3
)
UNION
SELECT RD.*
,RC.rcnId
,RC.ControlInfo
,RC.Performance
,RC.Design
,RC.ControlOwner
,RC.ControlNominee
,RC.ControlReviewer
,A.*
FROM @RiskData RD
INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
LEFT OUTER JOIN @ACTIONS A
ON (
A.AType = 2
AND RC.rcnId = A.ControlID
)
ORDER BY RD.RsdAnnualExp DESC
END
请您参考如下方法:
好吧，对于 Priyanka 来说可能为时已晚，但其他人可能会发现这很有用。
有一种方法可以使用一个小技巧在临时表上创建非唯一索引:添加一个标识列并将其作为主键的最后一个字段。
DECLARE @MyTable TABLE (IXField1 int, IXFiled2 int, Field3 bit, HelperIX int IDENTITY (1,1), PRIMARY KEY/UNIQUE (IXField1, IXField2, HelperIX)
