Can you use the 'Union' in a While loop?

[Knobbie]

I have a stored procedure that uses a cursor then goes through a while loop using data from the cursor. The query works and I get the data I need. The problem is that I can not put it into crystal or excel without doing A LOT of clean up work on the report because it executes over 40 queries through the loop and each query is 'seperate'. I've tried using the union at the end loop and it doesnt seem to work - my Question is can it be done?? or do I have to try another route??

Thanks in advance for any help.

here is an attachment of the stored procedure:

declare @count int,
@EmployerName char(60),
@ChangeDate datetime,
@Date_Const datetime

--declare the Cursor for getting the Employer name
declare @CrsrVar Cursor

set @CrsrVar = Cursor for
SELECT dbo.eligibilityorg.fullname AS employer_name
FROM dbo.enrollment INNER JOIN
dbo.claim INNER JOIN
dbo.claimdetail ON dbo.claim.claimid = dbo.claimdetail.claimid ON dbo.enrollment.enrollid = dbo.claim.enrollid INNER JOIN
dbo.program ON dbo.enrollment.programid = dbo.program.programid INNER JOIN
dbo.eligibilityorg ON dbo.enrollment.eligibleorgid = dbo.eligibilityorg.eligibleorgid
where (dbo.program.programid = 'QMXHPQ0375')
group by dbo.eligibilityorg.fullname
order by dbo.eligibilityorg.fullname

--open Cursor and get the first employer name and set it to the @EmployerName variable
Open @CrsrVar
fetch next from @CrsrVar
into @EmployerName

-- fetch status '0' means that the FETCH statement was successful.
while (@@fetch_status = 0)
begin
set @count = 0
set @Date_Const = '3/1/2001'
set @ChangeDate = dateadd(month,1,@Date_Const)
--while getdate() > @ChangeDate
while @ChangeDate < '12/31/2001'
begin
set @count = @count +1
print @count
SELECT Spread_date = 'March thru ' + DATENAME ( month , dateadd(month,-1,@ChangeDate) ),
sum(dbo.claimdetail.amountpaid) as Total_paid,
dbo.claim.status,
dbo.program.fullname,
dbo.eligibilityorg.fullname AS employer_name
FROM dbo.enrollment INNER JOIN
dbo.claim INNER JOIN
dbo.claimdetail ON dbo.claim.claimid = dbo.claimdetail.claimid ON dbo.enrollment.enrollid = dbo.claim.enrollid INNER JOIN
dbo.program ON dbo.enrollment.programid = dbo.program.programid INNER JOIN
dbo.eligibilityorg ON dbo.enrollment.eligibleorgid = dbo.eligibilityorg.eligibleorgid
where (dbo.eligibilityorg.fullname = @EmployerName)
and
(dbo.program.programid = 'QMXHPQ0375')
and
(dbo.claimdetail.dosfrom > CONVERT(DATETIME, '2001-03-01 00:00:00', 102))
and
(claim.paiddate >= @Date_Const and claim.paiddate < @ChangeDate)
--and
--dbo.eligibilityorg.fullname = 'Abtec, Inc.'
--(claim.paiddate >= '2001-04-01 00:00:00' and claim.paiddate < '2001-05-01 00:00:00')
group by dbo.program.programid, dbo.program.fullname, dbo.eligibilityorg.fullname, dbo.claim.status

union

set @ChangeDate = dateadd(month,1,@ChangeDate)

end

fetch next from @CrsrVar
into @EmployerName
end

close @CrsrVar
deallocate @CrsrVar

 

[rac2]

UNION requires matching columns in the two queries. It looks like you have five columns in the first query and one column in the second query.

Another thought, store the 40 seperate, intermediate results in a temporary table as you work through the cursor, then when all is done do one simple query to return all of the rows.

CREATE TABLE #results(col datatype, . . . )


DECLARE CsrVar CURSOR FOR blah blah blah
etc.
fetch

process
INSERT INTO #results (whatever columns you have values for)
repeat

CLOSE CsrVar
DEALLOCATE CsrVar

SELECT * FROM #results

Clear?

 

[Knobbie]

Thanks, I'll give that a shot