Union select statements within the while loop T-SQL

Question

I'm trying to use cursors to dynamically produce a result set. following is the code

        DECLARE           @ MilestoneName VARCHAR(100),
        @MilestoneSts  VARCHAR(100),
        @ProjectPre    VARCHAR(10),
        @ProjectID     VARCHAR(10),
        @Center        VARCHAR(20),
        @CenterPre     VARCHAR(20),
        @Source        VARCHAR(20),
        @Actual        INT;

SET @MilestoneName = null;
SET @MilestoneSts = null;
SET @ProjectPre = null;
SET @CenterPre  = null;

DECLARE s_cursor CURSOR FOR
SELECT ProjectID, Center, Source, Actual
FROM #MILESTONE


OPEN s_cursor
FETCH NEXT FROM s_cursor INTO @ProjectID, @Center, @Source, @Actual

WHILE @@FETCH_STATUS = 0
BEGIN

  SELECT @@FETCH_STATUS sts, @ProjectID PID, @Center Center, @Source Source, @Actual Actual 
  FETCH NEXT FROM s_cursor INTO @ProjectID, @Center, @Source, @Actual
END

CLOSE s_cursor
DEALLOCATE s_cursor

However using that I'm able to produce 79 results of single rows but I want to union all those rows into one result.. any possible solution will be highly appreciated..

Won't `SELECT ProjectID, Center, Source, Actual FROM #MILESTONE` do what you need? - i.e. Why are you doing row by row processing then joining the rows back together again? I assume this might be a simplification of your actual needs. If so can you tell us what those are so we can see if a cursor can be avoided. — Martin Smith, Feb 25 '11 at 15:37
Though I'm guessing from `@ProjectPre` that you are doing something with the values from the previous row. You could just insert into a tabgle variable then select from that at the end. Or you might want to explain what you are doing better so we can suggest alternatives. — Martin Smith, Feb 25 '11 at 15:51

score 0 · Answer 1 · edited May 23 '17 at 11:55

0

just checking, why are you using a cursor for this?

This sproc could be replaced by just saying

SELECT ProjectID, Center, Source, Actual
FROM #MILESTONE

But maybe I'm missing somehting here?

If there's logic you left out in your code look at this post: Multi-statement Table Valued Function vs Inline Table Valued Function

GJ

edited May 23 '17 at 11:55

Community

1
1

answered Feb 25 '11 at 15:38

gjvdkamp

9,929
3
38
46

My Supervisor gave me simplified version of his code and asked me to figure out a way to Union those results.. He takes data from 2 other tables into #milestones and wants to concantenate the fields before the result is publsihed.. – Avinash Feb 25 '11 at 16:03
1

Well the answer to you rquestion is to create a tablevariable where you insert the rows one by one, then return evrything from that table, but I don't think you asking the right question.I don;t think you need a cursor, it's a databse, not a normal prgamming language. If you could show us what these two table are and explain what needs to be doen we can do a lot more for you. – gjvdkamp Feb 25 '11 at 16:06
There is no reason at all to ever use a cursor for this. http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them – HLGEM Feb 25 '11 at 16:15
I understand cursor's are not the right way to do it.. I need to get access to that data.. I will post the tables info and the actual requirement soon.. Thank you very much for your support.. – Avinash Feb 25 '11 at 16:25

Union select statements within the while loop T-SQL

1 Answers1