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