Loop 2 sql server,

 

 

 

 USE [dw_antam]
GO
/****** Object:  StoredProcedure [dw].[InsdmExpMPlan]    Script Date: 05/10/2025 19:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dw].[InsdmExpMPlan]
-- Add the parameters for the stored procedure here
@year AS int,
@project AS varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @year_loop  AS int,
@project_loop AS varchar(max);

DECLARE cursor_year CURSOR FOR
SELECT DISTINCT Year FROM dw.FactExpMPlan
WHERE Year = IIF(ISNULL(@year, '') = '', Year, @year);

DECLARE cursor_project CURSOR FOR
SELECT DISTINCT ProjectID FROM dw.DimExpProject
WHERE ProjectID = IIF(ISNULL(@project, '') = '', ProjectID, @project);

OPEN cursor_year
FETCH NEXT FROM cursor_year INTO @year_loop

WHILE @@FETCH_STATUS=0
BEGIN

OPEN cursor_project
FETCH NEXT FROM cursor_project INTO @project_loop

WHILE @@FETCH_STATUS=0
BEGIN

DELETE FROM dm.ExpMPlan_Ori 
WHERE ProjectID = @project_loop 
AND Year = @year_loop;

INSERT INTO dm.ExpMPlan_Ori  ( ProjectID, Year, GeologyMapping, Drilling, Laboratorium )
SELECT @project_loop AS ProjectID, @year_loop AS Year, 
ISNULL((SELECT SUM(GeologyMapping) FROM dw.FactExpMPlan WHERE Year = @year_loop AND ProjectID = @project_loop), 0) AS GeologyMapping,
ISNULL((SELECT SUM(Drilling) FROM dw.FactExpMPlan WHERE Year = @year_loop AND ProjectID = @project_loop), 0) AS Drilling,
ISNULL((SELECT SUM(Laboratorium) FROM dw.FactExpMPlan WHERE Year = @year_loop AND ProjectID = @project_loop), 0) AS Laboratorium
FROM dw.DimExpProject AS p 
WHERE p.ProjectID =  @project_loop;

FETCH NEXT FROM cursor_project INTO @project_loop
END
CLOSE cursor_project

FETCH NEXT FROM cursor_year INTO @year_loop
END
CLOSE cursor_year

DEALLOCATE cursor_year
DEALLOCATE cursor_project

SELECT * FROM dm.ExpMPlan_Ori 
WHERE Year = IIF(ISNULL(@year, '') = '', Year, @year)
AND ProjectID = IIF(ISNULL(@project, '') = '', ProjectID, @project)

END

Tweet