First thing I would do is de-noodling the code so it can be readable:
a.) Format with proper indenting of code between BEGIN and END
b.) Remove the hoard of commented out code - if you want see the history - use source control system... Don't leave the rubbish in the code, or it very soon will contain more commented out lines than executable lines.
Next step would be removing redundant statements, see commented out lines with my initials (EE:)
So, after this exercise you will get something like:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Split_Membership_Yearss]
AS
DECLARE @RecordCount INT, @Count INT, @Memid uniqueidentifier,@OwnerId uniqueidentifier
DECLARE @start DATETIME, @end DATETIME, @SCHEME_ID uniqueidentifier, @EVENT_DATE DATETIME,@EVENT_TYPE_CODE nvarchar(20),@MemNo numeric, @CreatedOn datetime, @MembershipId uniqueidentifier
SET NOCOUNT ON
SELECT @RecordCount = count(*)
FROM [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
WHERE Proccess = 0 and DATEDIFF(Year, swmship_StartDate,isnull([swmship_EndDate],[RENEWAL_DATE]))>0
WHILE @RecordCount > 0
BEGIN
SELECT top 1 @MemNo=[MEMBER_NO], @Memid =[swmship_MemberId], @start = swmship_StartDate , @end =isnull(DATEADD(DAY,-1,[RENEWAL_DATE]),[swmship_EndDate]),
@SCHEME_ID =[SCHEME_ID], @CreatedOn = [CreatedOn], @MembershipID = [swmship_membershipId] ,
@EVENT_DATE=EVENT_DATE,@EVENT_TYPE_CODE=EVENT_TYPE_CODE
FROM [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
WHERE Proccess = 0 and DATEDIFF(Year, swmship_StartDate,isnull([swmship_EndDate],[RENEWAL_DATE]))>0
-- -- Get number of years
SET @Count=CEILING ((datediff(month, @start,@end) / 12.0)) --(Ceiling)round if there is months and days
-- EE: BEGIN - is useless here
INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),@start,
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: END - is useless here
WHILE @Count > 1
BEGIN
INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END
-- Pre 01/09/1981 And End Date Not Equal to 31/12 (dd/mm)
WHILE @Count = 1 and (
Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-31'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-12-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) != '31/12'
BEGIN
INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, (Select swmship_enddate From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS] Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn] ),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END
WHILE @Count = 1 and (Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-01'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-01-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) = '31/12'
BEGIN
INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END
-- -- Pre 01/09/1981 And No End Date
WHILE @Count = 1 and (
Select swmship_EndDate
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) is null
BEGIN
INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(d,-1,(Select RENEWAL_DATE From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS] Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn] )),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END
-- Pre 01/09/1981 And End Date Not Equal to 31/12 (dd/mm)
WHILE @Count = 1 and
(Select Cast(Datepart(d,isnull(swmship_EndDate,'1900-01-01'))as varchar) + '/' + Cast(Datepart(M,isnull(swmship_EndDate,'1900-01-01')) as varchar)
From [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
Where @MemNo=[MEMBER_NO] and @CreatedOn = [CreatedOn]) = '31/12'
BEGIN
INSERT [DATAMIGRATION].[dbo].[TEMP_SPLITED_MEMBERSHIPS]
([swmship_membershipId],
[swmship_EndDate],
[swmship_StartDate] ,
[swmship_MemberId] ,
[swmship_SchemeId] ,
[EVENT_DATE] ,
[EVENT_TYPE_CODE],
[MEMBER_NO] )
VALUES ( @MembershipID, DATEADD(YEAR, -(@Count-1), @end),DATEADD(day,+1,(DATEADD(year,-1,DATEADD(YEAR, -(@Count-1), @end)))),
@Memid, @SCHEME_ID, @EVENT_DATE, @EVENT_TYPE_CODE,@MemNo)
SET @Count = @Count-1
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END
SET @RecordCount = @RecordCount-1
UPDATE [DATAMIGRATION].[dbo].[TEMP_MERGE_MEMBERSHIPS]
SET [Proccess] =1
WHERE MEMBER_NO=@MemNo and SCHEME_ID=@SCHEME_ID and swmship_StartDate=@start
-- EE: CONTINUE - absolute stupid to have it here as there is no statements to ignore
END
GO
(If you click the appropriate tags in the left frame when posting, your post will be formatted much much better...)
Now you can start looking into loops...
The idea is to prepare all your data for a single update/insert of all rows, instead of update/insert per row as in loop.
If I have time I will start showing more.
Have you start to do anything so far? Please post!