Using UNION (ALL) in a WHILE loop with SELECTto merge the result set for each iteration

When I am looping with WHILE in SQL Server 2012, if I use a SELECT statement it prints the column name for each iteration. Can I UNION all?

Here is my sample query:

CREATE PROC spGetChar 
    @start int ,@end int
AS
BEGIN
WHILE (@start<@end)
BEGIN
--Print CHAR(@start);

SELECT CHAR(@start) AS Character;

SET @start=@start+ 1;
END
END;

Depending on your real requirements, something like this might work:

CREATE PROC spGetChar @start INT ,@end INT
AS
BEGIN
    DECLARE @Out varchar(255);
    SET @Out ='';
    WHILE (@start<@end)
    BEGIN
        SELECT @Out = @Out + CHAR(@start);
        SET @start = @start + 1;
    END
    SELECT @Out AS Character;
END;

This will append each char to a variable, then output the content of the variable. If you need each character on a separate line, you could add CHAR(13) + CHAR(10) to the end of the SELECT @Out line.

if you require each character to be returned in its own row, you could do:

CREATE PROC spGetChar @start INT ,@end INT
AS
BEGIN
    DECLARE @Out TABLE (Character varchar(255), RowNum IDENTITY(1,1) );
    WHILE (@start<@end)
    BEGIN
        INSERT INTO @Out (Character) VALUES ( CHAR(@start) );
        SET @start = @start + 1;
    END
    SELECT * FROM @Out ORDER BY RowNum;
END

This stores the character data in a table variable during each iteration of the while loop, then selects the contents of the table variable. The ORDER BY clause ensures the character data is outputted in the correct order since the RowNum field is an automatically incrementing IDENTITY column.

Could a recursive CTE satisfy your requirements? It can do the UNION ALL and recursion limiting, too.