SP Update market price

USE [AAB]
GO
/****** Object:  StoredProcedure [dbo].[SP_GET_UPDATE_MARKET_PRICE]    Script Date: 2/8/2017 1:52:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Iqbal Abdurrahman, update by Andri Kurniawan
-- Create date: 21 September 2016
-- Description: Get Data for Update Market Price [Vehicle Market Price Application Cisadane]
-- BRD : 0269 URF 2016 - Vehicle Market Price
-- =============================================
CREATE PROCEDURE [dbo].[SP_GET_UPDATE_MARKET_PRICE]
    @brand AS VARCHAR(MAX) ,
    @model AS VARCHAR(MAX) ,
    @geographical_area AS VARCHAR(MAX)
AS
    BEGIN

        DECLARE @tsqlUMP VARCHAR(MAX) = '';
        DECLARE @tsqlYearHeader VARCHAR(MAX) = '';
        DECLARE @tsqlYearPivot VARCHAR(MAX) = '';
        DECLARE @maxyear INT = 0;
        DECLARE @minyear INT = 0;
        SET @maxyear = YEAR(GETDATE());
        SET @minyear = YEAR(GETDATE()) - 14;

        DECLARE @mxP INT = @maxyear; -- mxP = Maximum Year For Dynamic Year in Pivot
        DECLARE @mxH INT = @maxyear; -- mnH = Minimum Year For Dynamic Year in Header
        DECLARE @mnP INT = @minyear; -- mnP = Maximum Year For Dynamic Year in Pivot
        DECLARE @mnH INT = @minyear; -- mnH = Maximum Year For Dynamic Year in Header

        SET @tsqlUMP = 'select Code as [VEHICLE_CODE], [SERIES],';

        WHILE @mxH >= @mnH
            BEGIN
                IF ( @mxH = @mnH )
                    BEGIN
                        SET @tsqlYearHeader = @tsqlYearHeader + 'coalesce(['
                            + CAST(@mxH AS VARCHAR) + '], 0) as ['
                            + CAST(@mxH AS VARCHAR) + '] ';
                    END
                ELSE
                    BEGIN
                        SET @tsqlYearHeader = @tsqlYearHeader + 'coalesce(['
                            + CAST(@mxH AS VARCHAR) + '], 0) as ['
                            + CAST(@mxH AS VARCHAR) + '],';
                    END
                SET @mxH = @mxH - 1;
            END
        PRINT ( @tsqlYearHeader );
        PRINT ( '--------------------------------------' );
        SET @tsqlUMP = @tsqlUMP + @tsqlYearHeader;
        SET @tsqlUMP = @tsqlUMP + 'from ( '      
        SET @tsqlUMP = @tsqlUMP
            + 'SELECT * ,
ISNULL(( SELECT TOP 1
MS.Series
FROM   dbo.Mst_Vehicle AS MS
WHERE  ms.Vehicle_code = cc.code
  ), '''') AS Series
 FROM      ( SELECT    *
 FROM      dbo.[SFN_GET_DATADOWNLOAD_MARKET_PRICE]('''+ @brand + ''' ,''' + @model + ''',''' + @geographical_area + ''')
 WHERE     tahun <= YEAR(GETDATE())
) AS CC
 UNION ALL
 SELECT    T.VEHICLE_CODE AS Code ,
YEAR(GETDATE()) - 14 AS Tahun ,
0 AS Harga ,
T.SERIES
 FROM      MST_VEHICLE T
 WHERE     T.STATUS = 1
AND T.BRAND_ID = ''' + @brand + '''
AND T.MODEL = ''' + @model + ''' '
        SET @tsqlUMP = @tsqlUMP + ') as P '
        SET @tsqlUMP = @tsqlUMP + 'PIVOT( '
        SET @tsqlUMP = @tsqlUMP + 'SUM(Harga) '
        SET @tsqlUMP = @tsqlUMP + 'FOR[Tahun] IN ( '

        WHILE @mxP >= @mnP
            BEGIN
                IF ( @mxP = @mnP )
                    BEGIN
                        SET @tsqlYearPivot = @tsqlYearPivot + '['
                            + CAST(@mxP AS VARCHAR) + '] ';
                    END
                ELSE
                    BEGIN
                        SET @tsqlYearPivot = @tsqlYearPivot + '['
                            + CAST(@mxP AS VARCHAR) + '], ';
                    END
                SET @mxP = @mxP - 1;
            END

        SET @tsqlUMP = @tsqlUMP + @tsqlYearPivot;
        SET @tsqlUMP = @tsqlUMP + ')'
        SET @tsqlUMP = @tsqlUMP + ') as PV'
        PRINT ( @tsqlUMP )

        EXEC (@tsqlUMP)

    END