While循环执行SQL查询异常：仅返回一行，需获取全部结果求助

Hey Robert, let's figure out why your WHILE loop is only running once and get you all the results you need!

First, let's break down the common issues that cause this problem, then walk through fixes—including both loop-based and more efficient set-based approaches (since SQL is built for set operations, we'll cover that too).

Common Reasons Your Loop Stops After One Run #

Most often, this happens because:

• You're only fetching the first row of your initial query result once, and never updating the value for the next iteration.
• Your loop condition isn't set up to check for new rows after each run.
• Your initial query is accidentally only returning one row (double-check for TOP 1 or overly strict filters!).

Solution 1: Fix the WHILE Loop with a Cursor #

If you really need to process rows one by one (e.g., for complex per-row logic), a cursor will let you iterate through every result from your first query. Here's how to set it up correctly:

-- Declare variables to store values from your first query
DECLARE @TargetId INT;

-- Create a cursor pointing to your full initial result set
DECLARE RowCursor CURSOR FOR
SELECT Id FROM YourFirstTable 
WHERE -- Add your initial filter conditions here (e.g., Status = 'Active')

-- Open the cursor to start accessing rows
OPEN RowCursor;

-- Grab the first row of data
FETCH NEXT FROM RowCursor INTO @TargetId;

-- Loop as long as we successfully fetch a row
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Run your second query using the value from the current row
    SELECT * 
    FROM YourSecondTable 
    WHERE ParentId = @TargetId;

    -- Critical step: Fetch the NEXT row for the next iteration
    FETCH NEXT FROM RowCursor INTO @TargetId;
END

-- Clean up the cursor (always do this!)
CLOSE RowCursor;
DEALLOCATE RowCursor;

The @@FETCH_STATUS variable is key here—it tells SQL if we successfully retrieved a new row. Without the second FETCH NEXT inside the loop, we'll keep reusing the first row's value forever (or exit immediately if the initial fetch fails).

Solution 2: Use a Set-Based Query (Better Performance!) #

SQL is optimized for working with sets of data, not individual rows. If your second query only needs to join with the first query's results, skip the loop entirely and use a JOIN instead—it's faster and simpler:

-- Get all matching rows in one go by joining the two tables
SELECT st.*
FROM YourFirstTable ft
INNER JOIN YourSecondTable st 
    ON st.ParentId = ft.Id
-- Add your filters here (e.g., WHERE ft.Category = 'Orders')

This will return every row from YourSecondTable that links to a row in YourFirstTable—no loops required.

Quick Troubleshooting Check #

Before you dive into code changes:

1. Run your initial query alone (SELECT Id FROM YourFirstTable ...) to confirm it returns multiple rows. If it only returns one, your loop is working as expected—fix the initial query's filters first!
2. Make sure you're not accidentally overwriting your loop variable outside the loop instead of updating it inside.

内容的提问来源于stack exchange，提问作者Robert