SQL table variable empty after while loop

Question

I have a SQL table I'm trying to query unique results. Based off of the "FileName" column I want to get only the most recent row for each filename. In the example, I am pulling all files with the last name of "smith". The LoanNumber may be in multiple rows because the file may have been copied and so I want the most recent one only.

The code below results in no data. I get just a column header called "FileID" and no values. I believe the @ResultsTable is not keeping the data I'm trying to put into it with the INSERT on the 12th line. I do not know why. I tried moving the DECLARE statement for the table variable @ResultsTable around and the best I can ever get it to display is a single record and most of places I put it, I only get "Must declare the table variable "@ResultsTable"."

What am I doing wrong that the table variable is not getting populated properly and maintaining it rows?

DECLARE @ResultsTable table(FileID varchar(10));    --table variable for the list of record IDs

DECLARE @ThisLoanNumber varchar(50);                --current loan number for each record to be used during while loop
DECLARE LoanFiles CURSOR read_only FOR              --create LoanFiles cursor to loop through
Select distinct [LoanNumber] from [Package_Files] 
Where [LastName]='smith';
OPEN LoanFiles;

While @@FETCH_STATUS = 0                            --If previous fetch was successful, loop through the cursor "LoanFiles"
    BEGIN
        FETCH NEXT FROM LoanFiles into @ThisLoanNumber; --Get the LoanNumber from the current row
        INSERT Into @ResultsTable                   --insert the ID number of the row which was updated most recently of the rows which have a loan number equal to the number form the current row in "LoanFiles" cursor   
        Select Top 1 [iFileID] From [Package_Files] Where [LoanNumber]=@ThisLoanNumber Order By [UpdateTime] Desc;
    END;
CLOSE LoanFiles;
DEALLOCATE LoanFiles;
Select * from @ResultsTable;                        --display results...

There is no need to use a loop for this.

Sean Lange
– Sean Lange

2016-02-23 21:26:09 +00:00
Commented Feb 23, 2016 at 21:26 — Sean Lange
– Sean Lange, Commented Feb 23, 2016 at 21:26

Sean Lange · Accepted Answer · 2016-02-23 21:29:26Z

1

There are a couple of ways you can do this type of query without resorting to looping.

Here is using a cte.

with SortedValues as
(
    select FileID
        , ROW_NUMBER() over (partition by LoanNumber order by UpdateTime desc) as RowNum
    from Package_Files
    where LastName = 'Smith'
)
select FileID
from SortedValues
where RowNum = 1

Another option is to use a subquery approach similar to this.

select FileID
from
(
    select FileID
        , ROW_NUMBER() over (partition by LoanNumber order by UpdateTime desc) as RowNum
    from Package_Files
    where LastName = 'Smith'
) x
where x.RowNum = 1

answered Feb 23, 2016 at 21:29

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Michael Over a year ago

Super simple... Thank you. I like the CTE version best. I didn't know the "ROW_NUMBER()" function.

Sean Lange Over a year ago

If this works for you then you should consider marking this as the answer.

TT. Over a year ago

You meant to say derived table, not subquery.

Michael Over a year ago

Sean, Yeah, I tried but it was too soon. I have marked it now. Thank you.

Collectives™ on Stack Overflow

SQL table variable empty after while loop

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related