Getting stuck with my Stored proc. Can anyone please help spot what am doing wrong here .
I am getting 2 sets of results but only want 1 set which is the end result ( Selecting the required columns) in Step 3.
1. Step 2 results
2.Step3 results
Use dbname
GO
CREATE PROC [dbo].[usp_ViewData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
---1.drop temp tables if it exist
IF OBJECT_ID('tempdb.dbo.#myTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable;
IF OBJECT_ID('tempdb.dbo.#myTempTable2', 'U') IS NOT NULL
DROP TABLE #MyTempTable2;
IF OBJECT_ID('tempdb.dbo.#ScopeTemptable', 'U') IS NOT NULL
DROP TABLE #ScopeTemptable;
--2-- Creates temp table by selecting into temp table
SELECT * INTO #MyTempTable FROM [dbo].[vw1 Data pt1]
SELECT * INTO #MyTempTable2 FROM [dbo].[vw2 Data pt2]
SELECT * INTO #ScopeTemptable FROM [dbo].[vw3 Scope]
------ Run the query for to join the results from the 3 views
SELECT * FROM #MyTempTable
INNER JOIN #MyTempTable2
ON #MyTempTable.ProjReturnID = #MyTempTable2.ProjReturnID
LEFT JOIN #ScopeTemptable ON #MyTempTable.ProjReturnID = #ScopeTemptable.ProjectReturnID
----------------------------------------------------------------------------------------------
--- 3. SELECT required data from the 3 #temptables
SELECT
t1.IDQ,
t1.[ID Number],
t1.ProjReturnID,
t2.[Benefits - Net Provided],
SC.CountOfProjScopeChngNo,
t2.[Start Date - Variance (Days)],
t2.[EndDate - Variance (Days)],
t2.[End Date - Variance (%)]
FROM #MyTempTable t1
INNER JOIN #MyTempTable2 t2
ON t1.ProjReturnID = t2.ProjReturnID
LEFT JOIN ScopeTemtable Sc ON t1.ProjReturnID = Sc.ProjectReturnID
---4. Drop temp tables
DROP TABLE #MyTempTable;
DROP TABLE #ScopeTemptable;
DROP TABLE #MyTempTable2
END
I am getting 2 sets of results but only want 1 set which is the end result ( Selecting the required columns) in Step 3.
1. Step 2 results
2.Step3 results
Use dbname
GO
CREATE PROC [dbo].[usp_ViewData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
---1.drop temp tables if it exist
IF OBJECT_ID('tempdb.dbo.#myTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable;
IF OBJECT_ID('tempdb.dbo.#myTempTable2', 'U') IS NOT NULL
DROP TABLE #MyTempTable2;
IF OBJECT_ID('tempdb.dbo.#ScopeTemptable', 'U') IS NOT NULL
DROP TABLE #ScopeTemptable;
--2-- Creates temp table by selecting into temp table
SELECT * INTO #MyTempTable FROM [dbo].[vw1 Data pt1]
SELECT * INTO #MyTempTable2 FROM [dbo].[vw2 Data pt2]
SELECT * INTO #ScopeTemptable FROM [dbo].[vw3 Scope]
------ Run the query for to join the results from the 3 views
SELECT * FROM #MyTempTable
INNER JOIN #MyTempTable2
ON #MyTempTable.ProjReturnID = #MyTempTable2.ProjReturnID
LEFT JOIN #ScopeTemptable ON #MyTempTable.ProjReturnID = #ScopeTemptable.ProjectReturnID
----------------------------------------------------------------------------------------------
--- 3. SELECT required data from the 3 #temptables
SELECT
t1.IDQ,
t1.[ID Number],
t1.ProjReturnID,
t2.[Benefits - Net Provided],
SC.CountOfProjScopeChngNo,
t2.[Start Date - Variance (Days)],
t2.[EndDate - Variance (Days)],
t2.[End Date - Variance (%)]
FROM #MyTempTable t1
INNER JOIN #MyTempTable2 t2
ON t1.ProjReturnID = t2.ProjReturnID
LEFT JOIN ScopeTemtable Sc ON t1.ProjReturnID = Sc.ProjectReturnID
---4. Drop temp tables
DROP TABLE #MyTempTable;
DROP TABLE #ScopeTemptable;
DROP TABLE #MyTempTable2
END
Last edited: