Loop and Temp Table usage in SQL SERVER

Loop and Temp Table usage in SQL SERVER 


Target :
To use Loop(may also use nested loop when needed) to fetch data then save into result table ,return result table


steps :
1.declare a result table
2.finish the data source query and save into #temptable
3.while(#temptable have data ) delete one row from #temptable
4.fetch data ,then fill in result table


sample codes :

BEGINDECLARE @FrmMstId UNIQUEIDENTIFIERset @FrmMstId ='0E38D831-AC9D-4DEC-9422-11998A834269'DECLARE  @FieldsDataTable TABLE(SetID int,FieldName varchar(200),Value varchar(max))DECLARE @SetID AS int = 1DECLARE @Item AS nvarchar(20)DECLARE @Matter AS nvarchar(200)DECLARE @CostClaimed AS float = 0DECLARE @IsAgree AS int = 0DECLARE @ProposedAmt AS float = 0DECLARE @BocBreakdown AS nvarchar(200)--fetch boc itemIF Object_id('tempdb..#T1') IS NOT NULL  DROP TABLE #T1  SELECT fbi.Id ,bi.Item,bi.Matter ,fbi.CostClaimed,fbi.IsAgree,fbi.ProposedAmtINTO #T1FROM CM.FrmMst fm JOIN HM.FrmBoc fb ON fm.Id = fb.FrmMstFKJOIN HM.FrmBocItem fbi ON fbi.FrmBocFK = fb.IdJOIN HM.BocItem bi ON bi.Id = fbi.BocItemFKWHERE fm.Id = @FrmMstId AND (bi.ExpiryDate > getdate() AND bi.effectiveDate <= getdate())AND fb.DeletedBy IS NULL ORDER BY CASE    WHEN IsNumeric(bi.Item) = 1 THEN Right('0000000000' + bi.Item + '0', 10)    ELSE Right('0000000000' + bi.Item, 10)    END--process DECLARE @FrmBocItemId AS UNIQUEIDENTIFIERWHILE (SELECT Count(*) FROM #T1) > 0BEGIN    SELECT TOP 1     @FrmBocItemId = Id,    @Item = Item,    @Matter = Matter,    @CostClaimed = CostClaimed,    @IsAgree = IsAgree,    @ProposedAmt = ProposedAmtFROM #T1INSERT INTO @FieldsDataTable values(@SetID, 'Item',ISNULL(@Item,''));INSERT INTO @FieldsDataTable values(NULL, 'Matter',ISNULL(@Matter,''));INSERT INTO @FieldsDataTable values(NULL, 'CostClaimed',@CostClaimed);INSERT INTO @FieldsDataTable values(NULL, 'IsAgree',isNULL(@IsAgree,0));INSERT INTO @FieldsDataTable values(NULL, 'Proposed Amount',isNULL(@ProposedAmt,0));--process break down data--step 1: check temp table is not existIF Object_id('tempdb..#T2') IS NOT NULLDROP TABLE #T2--step 2:fetch data into temp tableDECLARE @BreakdownId uniqueidentifier;SELECT Id,BocItemBreakdownDesc INTO #T2 FROM HM.FrmBocItemBreakdown WHERE FrmBocItemFK = @FrmBocItemId;--step 3:loop fetch dataWHILE (SELECT Count(*) FROM #T2) > 0BEGINSELECT TOP 1 @BreakdownId = Id,@BocBreakdown = BocItemBreakdownDesc FROM #T2INSERT INTO @FieldsDataTable values(NULL, '',ISNULL(@BocBreakdown,''));DELETE #T2 WHERE Id = @BreakdownIdENDSET @SetID = @SetID + 1;DELETE #T1 WHERE Id = @FrmBocItemIdENDEND