SQL查询如何实现多行输出？现有变量赋值查询无法满足需求

Got it, let's break down why your current query isn't working as expected and how to fix it.

The Problem with Your Current Code #

When you run Select @divisionid = code from tbl_em_employees Where code between 10001 and 10020, you’re assigning each matching code value to the same @divisionid variable one after another. By the time the query finishes, the variable only holds the last value from the result set (probably 10020), which is why your Print statement only shows that single number.

Solution 1: Direct SELECT (Simplest & Most Efficient) #

If you just need to output each code as a separate row (the standard use case for this kind of query), you don’t need a variable at all. Just run a basic SELECT:

SELECT code 
FROM tbl_em_employees 
WHERE code BETWEEN 10001 AND 10020;

This will return every matching code value in its own row—exactly what you’re looking for.

Solution 2: Using a Cursor to Print Each Value (If You Need PRINT Specifically) #

If you must use the PRINT statement (e.g., in a script where you need text output to the console), use a cursor to iterate through each row and print the code:

DECLARE @divisionid varchar(max);

-- Declare a cursor for the matching codes
DECLARE code_cursor CURSOR FOR
SELECT code 
FROM tbl_em_employees 
WHERE code BETWEEN 10001 AND 10020;

OPEN code_cursor;
FETCH NEXT FROM code_cursor INTO @divisionid;

-- Loop through each row and print the code
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @divisionid;
    FETCH NEXT FROM code_cursor INTO @divisionid;
END;

-- Clean up the cursor
CLOSE code_cursor;
DEALLOCATE code_cursor;

Solution 3: Temp Table + WHILE Loop (Alternative to Cursor) #

Another approach is to store results in a temporary table, then loop through it to print each value:

-- Create a temp table to hold codes with row numbers
CREATE TABLE #TempCodes (code varchar(max), RowNum INT IDENTITY(1,1));

-- Insert matching codes into the temp table
INSERT INTO #TempCodes (code)
SELECT code 
FROM tbl_em_employees 
WHERE code BETWEEN 10001 AND 10020;

DECLARE @maxRow INT = (SELECT MAX(RowNum) FROM #TempCodes);
DECLARE @currentRow INT = 1;
DECLARE @divisionid varchar(max);

-- Loop through each row and print
WHILE @currentRow <= @maxRow
BEGIN
    SELECT @divisionid = code FROM #TempCodes WHERE RowNum = @currentRow;
    PRINT @divisionid;
    SET @currentRow = @currentRow + 1;
END;

-- Clean up the temp table
DROP TABLE #TempCodes;

Which Solution Should You Pick? #

• Use Solution 1 if you just need to retrieve the data (this is the standard, most performant way in SQL).
• Use Solution 2 or 3 only if you specifically need to print each value to the console (e.g., in a SSMS script).

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