SQL Server Partition Scheme automatic maintenance script

CREATE PROC Proc_partition_deal
As
DECLARE @LatestValue DATETIME,
@LastValue DATETIME,
@EarlValue DATETIME,
@differ INT,
@New_Range_Value DATETIME,
@fileGroupName VARCHAR (200),
@sql VARCHAR (1000),
@P_Sch_Name VARCHAR (100),
@P_Fun_Name VARCHAR (100),
@P_Fun_Id INT

SET @fileGroupName = ' [PRIMARY] '
SET @P_Sch_Name = ' sche_part_date '
SET @P_Fun_Name = ' func_part_date '
SET @sql = ' '

IF not EXISTS (SELECT 1
From
Sys.partition_functions
WHERE name = @P_Fun_Name)
RETURN;

IF not EXISTS (SELECT 1
From
Sys.partition_schemes
WHERE name = @P_Sch_Name)
RETURN;

SELECT @P_Fun_Id = function_id
From
Sys.partition_functions
WHERE name = @P_Fun_Name

--Merge partition functions
SELECT @EarlValue = CONVERT (VARCHAR (), Min (value), 120)
From
Sys.partition_range_values
WHERE function_id = @P_Fun_Id

SET @sql = ' ALTER Partition FUNCTION ' + @P_Fun_Name
+ ' () MERGE Range (' '
+ CONVERT (VARCHAR), @EarlValue, 120)
+ '. 000 ' + ') '

EXEC (@sql)

--print @sql
--Increase the partitioning scheme
SET @sql = ' '
SET @sql = ' ALTER PARTITION SCHEME ' + @P_Sch_Name
+ ' NEXT used ' + @fileGroupName

EXEC (@sql)

--print @sql
--Add partition function
SELECT @LastValue = CONVERT (VARCHAR (), Min (value), 120),
@LatestValue = CONVERT (VARCHAR (), Max (value), 120)
From
(SELECT TOP 2 value
From
Sys.partition_range_values
WHERE function_id = @P_Fun_Id
ORDER by VALUE DESC) PRV

SET @differ =datediff (Dd, @LastValue, @LatestValue)
SET @New_Range_Value =dateadd (Dd, @differ, CONVERT (DATE, @LatestValue))
SET @sql = ' '
SET @sql = ' ALTER Partition FUNCTION ' + @P_Fun_Name
+ ' () Split Range (' '
+ CONVERT (VARCHAR), @New_Range_Value, 120)
+ '. 000 ' + ') '

EXEC (@sql)
--print @sql

SQL Server Partition Scheme automatic maintenance script