All geek questions in one place

SQL SERVER Nonclustered index in a table variable?

How to create a non-clustered index for a table variable?

Create @risk table (rskid int)

Create a non-clustered r_rskid_nc index on @risk (rskid)

This does not work?

My program

How can I optimize it?

ALTER PROCEDURE [dbo]. [SPR_LV004] (@TopN INT, @LoggedUserId INT, @ Object VARCHAR (255), @OpModel VARCHAR (255), @RiskCat VARCHAR (255), @RsdlInh VARCHAR (1), @DisplayAction VARCHAR (1), @DisplayAction VARCHAR (1), ) AS

  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

Please, help!

Thanks in advance

+3
source share
5 answers

Well, it's already too late for Priyanka, but someone might find this helpful.

temp, : .

DECLARE @MyTable TABLE (IXField1 int, IXFiled2 int, Field3 bit, HelperIX int IDENTITY (1,1), PRIMARY KEY/UNIQUE (IXField1, IXField2, HelperIX)
+11

Variable, , PRIMARY KEY, ( , ):

DECLARE @risk TABLE (rskid int PRIMARY KEY)

, , .

.

+2

, , , PRIMARY KEY UNIQUE. , , , .

, :

declare @t table (
    ID int not null,
    Val1 varchar(10) not null,
    PRIMARY KEY NONCLUSTERED (ID),
    UNIQUE CLUSTERED (Val1)
)
insert into @t (ID,Val1)
select 1,'abc'

select * from @t
+1
source

You cannot add an index to a table variable.

You can add primary keys and unique restrictions.

0
source

I don’t know which version you are using, but I am using SQL Server 2016 and it is as simple as

    declare @Stock table
    (
        StockId     int     NOT NULL
    ,   FieldId     int     NOT NULL
    ,   StockDate   date    NOT NULL
    ,   StockValue  float   NULL
    ,   INDEX IX_Priyanka NONCLUSTERED(StockId, FieldId, StockDate desc) 
    )

Hope it covers some aspects of what you are looking for.

0
source

Source: https://habr.com/ru/post/1784486/

More articles:


All Articles