title: SQL SERVER NON-Clustered Index on table variable?
body: <p>How can we create non-clustered index on table variable?</p>
  
  <p><strong>Create Table @risk 
  (rskid int)</strong></p>
  
  <p><strong>Create nonclustered index r_rskid_nc on @risk(rskid)</strong></p>
  
  <p>It does not work??</p>
  
  <hr>
  
  <h2><strong>My proc</strong></h2>
  
  <p><strong><em>How can i optimize it ??</em></strong></p>
  
  <p>ALTER PROCEDURE [dbo].[SPR_LV004]
     (
      @TopN INT
     ,@LoggedUserId INT
     ,@Entity VARCHAR(255)
     ,@OpModel VARCHAR(255)
     ,@RiskCat VARCHAR(255)
     ,@RsdlInh VARCHAR(1)
     ,@DisplayAction VARCHAR(1)
     ,@LastAssDate DATETIME
     )
  AS </p>
  
  <pre><code>  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 &lt;= @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 &lt;= @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 &lt;= @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 &lt;= @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) &lt;= 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 &lt;= @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) &gt; 1900)
                  AND (
  
                        A.actState = 4
                      )
             THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
             ELSE NULL
          END 
          AS actTgtCompleteDate
          ,CASE WHEN (YEAR(A.actTargetDate) &gt; 1900)
                  AND (A.actState &lt; 3)
                  AND (A.actTargetDate &lt;= @ThisDate)
             THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
             ELSE NULL
          END AS actTgtODueDate
          ,CASE WHEN (YEAR(A.actTargetDate) &gt; 1900)
                  AND (A.actState &lt; 3)
                  AND (A.actTargetDate &lt;= @ThisDate)
             THEN NULL --'overdue'
             WHEN YEAR(A.actTargetDate) &gt; 1900
                  AND (
                       A.actState = 3
                       OR A.actState = 4
                      ) THEN NULL --'complete'
             WHEN YEAR(A.actTargetDate) &lt;= 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 &lt; 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) &gt; 1900)
                  AND (
                      -- A.actState &gt; 2                        AND 
                          A.actState &lt;= 4
                      )
             THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
             ELSE NULL
          END 
          AS actTgtCompleteDate
          ,CASE WHEN (YEAR(A.actTargetDate) &gt; 1900)
                  AND (A.actState &lt; 3)
                  AND (A.actTargetDate &lt;= @ThisDate)
             THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
             ELSE NULL
          END AS actTgtCompleteDate
          ,CASE WHEN (YEAR(A.actTargetDate) &gt; 1900)
                  AND (A.actState &lt; 3)
                  AND (A.actTargetDate &lt;= @ThisDate)
             THEN NULL --'overdue'
             WHEN YEAR(A.actTargetDate) &gt; 1900
                  AND (
                       A.actState = 3
                       OR A.actState = 4
                      ) THEN NULL --'compete'
             WHEN YEAR(A.actTargetDate) &lt;= 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 &lt; 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
  </code></pre>