LinkedIn

Friday, 14 March 2008

My sample cursor

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[COPY_MASTER]

@scenarioID int,
@NewScenarioName varchar(50)

AS
BEGIN

DECLARE @NewScenarioID int

DECLARE @CurrentPropertyID int
DECLARE @NewPropertyID int

DECLARE @CurrentAssetID int

DECLARE @NewAssetID int

DECLARE @CurrentHeadleaseID int
DECLARE @CurrentSubleaseID int

SET NOCOUNT ON;

-- COPY SCENARIO
print ' - BEGINNING TO COPY SCENARIO - ' + Convert(varchar(20), @scenarioID)

EXEC COPY_Scenario @scenarioID, @NewScenarioName, @NewScenarioID OUTPUT
DECLARE Properties_Cursor CURSOR FOR

SELECT propertyID
FROM [Property]
WHERE ScenarioID = @scenarioID

OPEN Properties_Cursor

FETCH NEXT FROM Properties_Cursor
INTO @CurrentPropertyID

WHILE @@FETCH_STATUS = 0

BEGIN
print '====================================================='
print ' -- The current propertyID is - ' + Convert(varchar(20), @CurrentPropertyID)

EXEC COPY_Properties @scenarioID, @NewScenarioID, @CurrentPropertyID, @NewPropertyID OUTPUT

-- GET ALL ASSETS TO LOOP THROUGH ------------------------
DECLARE Assets_Cursor CURSOR FOR
SELECT DISTINCT AssetID from [asset] WHERE propertyID = @CurrentPropertyID

OPEN Assets_Cursor

FETCH NEXT FROM Assets_Cursor
INTO @CurrentAssetID

WHILE @@FETCH_STATUS = 0

BEGIn
print ''
print ' --- The current assetID is - ' + Convert(varchar(20), @CurrentAssetID)

EXEC COPY_Assets @NewPropertyID, @CurrentAssetID, @NewAssetID OUTPUT

EXEC COPY_Headleases @CurrentAssetID, @NewAssetID, @CurrentHeadleaseID OUTPUT

IF @CurrentHeadleaseID > 0
BEGIN
print ' ----- The current HeadleaseID is - ' + Convert(varchar(20), @CurrentHeadleaseID)
END
ELSE
print ' ----- NO HEADLEASE'

EXEC COPY_Subleases @CurrentAssetID, @NewAssetID, @CurrentSubleaseID OUTPUT

IF @CurrentSubleaseID > 0
BEGIN
print ' ----- The current SubleaseID is - ' + Convert(varchar(20), @CurrentSubleaseID)
END
ELSE
print ' ----- NO SUBLEASE'

FETCH NEXT FROM Assets_Cursor
INTO @CurrentAssetID
END

CLOSE Assets_Cursor
DEALLOCATE Assets_Cursor
---------------------------------------------------------




FETCH NEXT FROM Properties_Cursor
INTO @CurrentPropertyID
END

CLOSE Properties_Cursor
DEALLOCATE Properties_Cursor

print '*******************************************************'
print '*******************************************************'
print '***** THE SCENARIO - ' + Convert(varchar(20), @scenarioID) + ' WAS COPIED SUCCESSFULLY ****'
print '*******************************************************'
print '*******************************************************'

END