w0051977
w0051977

Reputation: 15807

Return one table from cursor

Please see the code below:

    DECLARE @ID int
DECLARE @errorflag int

DECLARE Warning_Cursor CURSOR FOR
    SELECT TOP 3 ID FROM Warnings
    SET @errorflag = @errorflag + @@Error  
    OPEN Warning_cursor
        SET @errorflag = @errorflag + @@Error  
        FETCH NEXT FROM Warning_cursor INTO @ID
        WHILE @@FETCH_STATUS = 0  
    begin
        SELECT @ID
        FETCH NEXT FROM Warning_cursor INTO @ID
    END
CLOSE Warning_cursor
DEALLOCATE Warning_cursor

The cursor returns three tables with one row each. How can I return one table with three rows?

Upvotes: 1

Views: 5707

Answers (2)

Jodrell
Jodrell

Reputation: 35716

Why don't you just do,

SELECT TOP 3 ID FROM Warnings

More generally, if you are using a cursor, you are probably doing it wrong.


If you really have to use a cursor for some reason that is not part of the question. You could do

DECLARE @Id int;
DECLARE @Ids TABLE (Id Int);
DECLARE Warning_Cursor CURSOR FOR SELECT TOP 3 ID FROM Warnings;
OPEN Warning_cursor; 
FETCH NEXT FROM Warning_cursor INTO @Id;
WHILE @@FETCH_STATUS = 0 BEGIN
    INSERT @Ids SELECT @Id;

    FETCH NEXT FROM Warning_cursor INTO @Id;
END
CLOSE Warning_cursor;
DEALLOCATE Warning_cursor;
SELECT Id FROM @Ids;

Upvotes: 1

w0051977
w0051977

Reputation: 15807

The answer was to create a temporary table as follows:

   DECLARE @ID int
DECLARE @errorflag int
DECLARE @CONCATRESULT TABLE (ID INT)
DECLARE Warning_Cursor CURSOR FOR
    SELECT TOP 3 ID FROM Warnings
    SET @errorflag = @errorflag + @@Error  
    OPEN Warning_cursor
        SET @errorflag = @errorflag + @@Error  
        FETCH NEXT FROM Warning_cursor INTO @ID
        WHILE @@FETCH_STATUS = 0  
    begin
        INSERT into @CONCATRESULT (ID) VALUES (@ID)
        FETCH NEXT FROM Warning_cursor INTO @ID
    END
CLOSE Warning_cursor
DEALLOCATE Warning_cursor
select id from @CONCATRESULT 

Upvotes: 0

Related Questions