1

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;
1

2 Answers 2

1

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.

2
  • is it correct way to use #table instead of table variable?
    – lata
    Commented Jul 15, 2014 at 5:28
  • Sure you could use a temporary table instead. It works essentially the same way. A #temp table may perform better, depending on your actual data requirements.
    – Hannah Vernon
    Commented Jul 15, 2014 at 13:57
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.