Thanks all for your suggestions.
I couldn't figure out how to do a UNION for this, but I came across an
article suggesting using #TempTables, which I thought was a GRAND idea.
Here is what I finished up with. If anyone would care to reword this Stored
Proc into something better, I'd be very appreciative!
Thanks again.
--Here's the result:
Promotion State Build Label
Date Started
-------------------------------------------------- -------------------------
------------------------- ------------
QA 01 corm_01_09_01_999
Sep 20 2003
Prod corm_01_09_01_001
Sep 8 2003
--And here is the query:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure usp_GetBuildSummaryByCompId2
@CompID int =5
as
declare @EnvName varchar(25)
declare @EnvID int
declare Environments_Cursor CURSOR FOR
-- Create Temp table to hold Build Summary results
select e.EnvName , e.EnvironmentID from _ProductEnvironments pe
join _Environments e on pe.EnvironmentID = e.EnvironmentID
where CompID=5
order by LayerOrder
OPEN Environments_Cursor
--Do first Fetch
FETCH NEXT FROM Environments_Cursor
INTO @EnvName, @EnvID
-- Do first query to set up table
select top 1 e.EnvName as 'Promotion State', pb.BuildLabel as 'Build Label',
CONVERT(char(12), bp.PromotionDate) as 'Date Started' into #TempTable
from _products p
join _ProductComponents pc on p.ProductID = pc.ProductID
join _ProductBuilds pb on pc.CompID = pb.CompID
join _BuildPromotions bp on pb.BuildID = bp.BuildID
join _ProductEnvironments pe on bp.ProdEnvID = pe.ProdEnvID
join _Environments e on pe.EnvironmentID = e.EnvironmentID
where pc.CompID = @CompID and e.EnvironmentID= @EnvID
order by PromotionDate Desc
-- Get next Fetch
FETCH NEXT FROM Environments_Cursor
INTO @EnvName, @EnvID
-- Check Fetch status to see if there are more rows avail.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Find out the build details for each layer
Insert Into #TempTable
select top 1 e.EnvName as 'Promotion State', pb.BuildLabel as 'Build
Label', CONVERT(char(12), bp.PromotionDate) as 'Date Started'
from _products p
join _ProductComponents pc on p.ProductID = pc.ProductID
join _ProductBuilds pb on pc.CompID = pb.CompID
join _BuildPromotions bp on pb.BuildID = bp.BuildID
join _ProductEnvironments pe on bp.ProdEnvID = pe.ProdEnvID
join _Environments e on pe.EnvironmentID = e.EnvironmentID
where pc.CompID = @CompID and e.EnvironmentID= @EnvID
order by PromotionDate Desc
-- Get Next EnvID
FETCH NEXT FROM Environments_Cursor
INTO @EnvName, @EnvID
END
select * from #TempTable
-- Close Cursor and clean up
CLOSE Environments_Cursor
DEALLOCATE Environments_Cursor
drop table #TempTable
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"William (Bill) Vaughn" <No***************@nwlink.com> wrote in message
news:uf*************@TK2MSFTNGP11.phx.gbl...
I would investigate the UNION query to create a single rowset on the
server. This would simplify the server-side of the operation as well as make it
easy to use the Fill method to construct the DataTable. I don't use the
DataReader to construct DataTable objects--it's too slow when compared
with Fill. The DataTable can be bound to the DataGrid quite easily.
hth
--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights. __________________________________
"M. Craig" <mc****@pugetsoundmicro.com> wrote in message
news:eH**************@TK2MSFTNGP12.phx.gbl... I'm trying to display multiple resultsets, returned in a SQLDataReader,
in a single table. The number of resultsets returned is variable, usually 3
or 4. Basically, each resultset has 1 row and I'd like to display each row as
a row in the same table. I'd like to use the DataGrid if possible.
Here is my original idea:
1. Get returned DataReader
2. For each DataSet returned, create a DataGrid control, populate
and display
a. First DataGrid will have a header showing column names
b. Following DataGrids will have no header
Any ideas or places I can do more reading?
Thanks,
Mike
