Change temporary table to table varible using sqlserver 2012

Question

I want to remove the #temp1 from my query and use it as another table varible that work same as it is working using #temp1 table. Here is my query

DROP TABLE #temp1
;WITH cteSales AS(
    SELECT BillDate,
           BillNo,
           '-' AS     NarrationNumber,
           TotalAmount,
           0       AS ReceivedAmount
    FROM   SalesMaster
    WHERE  SalesMaster.ClientID = @ClientID
           AND SalesMaster.BillDate BETWEEN @FromDate AND @ToDate
    UNION ALL 
    SELECT [Date]  AS BillDate,
           SalesAccount.BillNo,
           SalesAccount.NarrationNumber,
           0       AS TotalAmount,
           Amount  AS ReceivedAmount
    FROM   SalesAccount
           LEFT JOIN SalesMaster sm
                ON  sm.BillNo = SalesAccount.BillNO
    WHERE  sm.ClientID = @ClientID
           AND sm.BillDate BETWEEN @FromDate AND @ToDate
), cteFormattedSales AS(
    SELECT ROW_NUMBER() OVER(ORDER BY BillNo ASC, BillDate ASC) AS RowNum,
           *
    FROM   cteSales
)

SELECT cfs1.RowNum,
       cfs1.BillDate,
       cfs1.BillNo,
       cfs1.NarrationNumber,
       cfs1.TotalAmount,
       cfs1.ReceivedAmount,
       (cfs1.TotalAmount - cfs1.ReceivedAmount) AS DueAmount INTO #temp1
FROM   cteFormattedSales cfs1
DECLARE @BillDate date,
        @PrevBillNo INT,
        @NextBillNo INT,
        @NarrationNumber VARCHAR(15),
        @TotalAmount DECIMAL(18, 2),
        @ReceivedAmount DECIMAL(18, 2),
        @NextDue DECIMAL(18, 2),
        @PrevDue DECIMAL(18, 2)
DECLARE @finalTable TABLE(
            BillDate date,
            BillNo INT,
            NarrationNumber NVARCHAR(15),
            TotalAmount DECIMAL(18, 2),
            ReceivedAmount DECIMAL(18, 2),
            DueAmount DECIMAL(18, 2)
        )

DECLARE @TotalRecords        INT,
        @Counter             INT = 1,
        @CarryOverAmount     DECIMAL(18, 2)

SELECT @TotalRecords = COUNT(*)
FROM   #temp1

SELECT @BillDate = t.BillDate,
       @PrevBillNo          = t.BillNo,
       @NarrationNumber     = t.NarrationNumber,
       @TotalAmount         = t.TotalAmount,
       @ReceivedAmount      = t.ReceivedAmount,
       @PrevDue             = t.DueAmount
FROM   #temp1 t
WHERE  t.RowNum = @Counter

WHILE @Counter <= @TotalRecords
BEGIN
    SELECT @BillDate = t.BillDate,
           @NextBillNo          = t.BillNo,
           @NarrationNumber     = t.NarrationNumber,
           @TotalAmount         = t.TotalAmount,
           @ReceivedAmount      = t.ReceivedAmount,
           @NextDue             = t.DueAmount
    FROM   #temp1 AS t
    WHERE  t.RowNum = @Counter

    IF (@Counter = 1)
        SET @CarryOverAmount = @TotalAmount

    SET @PrevDue = @CarryOverAmount - @ReceivedAmount

    IF @PrevBillNo <> @NextBillNo
    BEGIN
        SET @PrevBillNo = @NextBillNo
        SET @CarryOverAmount = @TotalAmount + @PrevDue
        SET @PrevDue = 0
    END

    INSERT INTO @finalTable
      (
        BillDate,
        BillNo,
        NarrationNumber,
        TotalAmount,
        ReceivedAmount,
        DueAmount
      )
    VALUES
      (
        @BillDate,
        @PrevBillNo,
        @NarrationNumber,
        @TotalAmount,
        @ReceivedAmount,
        CASE 
             WHEN @NarrationNumber = '-' THEN @CarryOverAmount
             ELSE @PrevDue
        END
      )

    IF @PrevBillNo = @NextBillNo
    BEGIN
        IF @Counter > 1
        BEGIN
            SET @CarryOverAmount = CASE 
                                        WHEN @NarrationNumber = '-' THEN @CarryOverAmount 
                                             + @PrevDue
                                        ELSE @CarryOverAmount - @ReceivedAmount
                                   END
        END
    END

    SET @Counter = @Counter + 1
END 
SELECT *
FROM   @finalTable

Here the code that i get error on invalid ReceivedAmount

SELECT @BillDate = t.BillDate,
           @PrevBillNo          = t.BillNo,
           @NarrationNumber     = t.NarrationNumber,
           @TotalAmount         = t.TotalAmount,
           @ReceivedAmount      = t.ReceivedAmount,
           @PrevDue             = t.DueAmount
    FROM   @Temp1 t
    WHERE  t.RowNum = @Counter

    WHILE @Counter <= @TotalRecords
    BEGIN
        SELECT @BillDate = t.BillDate,
               @NextBillNo          = t.BillNo,
               @NarrationNumber     = t.NarrationNumber,
               @TotalAmount         = t.TotalAmount,
               @ReceivedAmount      = t.ReceivedAmount,
               @NextDue             = t.DueAmount
        FROM   @Temp1 AS t
        WHERE  t.RowNum = @Counter

Answer 1

You need to do just 3 steps:

A. DECLARE table variable

-- correct types of columns
DECLARE @temp1 TABLE(
    RowNum int
    ,BillDate datetime
    ,BillNo int
    ,NarrationNumber int
    ,TotalAmount money
    ,RecievedAmount money
    ,DueAmount   money
)

B. Rewrite SELECT ... INTO -> INSERT INTO .. SELECT

INSERT INTO @temp1
SELECT cfs1.RowNum,
       cfs1.BillDate,
       cfs1.BillNo,
       cfs1.NarrationNumber,
       cfs1.TotalAmount,
       cfs1.ReceivedAmount,
       (cfs1.TotalAmount - cfs1.ReceivedAmount) AS DueAmount 
FROM   cteFormattedSales cfs1   
... etc ...

C. Replace all #temp1 to @temp1