store nvarchar larger than 4000 in sql 2000

Question

I'm trying to execute sql using @SumSQL var type nvarchar 4000 , when i enter a value of parameter in my stored procedure larger than a specific value like '11' the program return sql error ... the problem comes that when the sql store the char in the var @SumSQL it size become larger than 4000 then it store nothing after that , in sql 2008 if i changed the type of nvarchar (4000) to nvarchar (max) it works great ,but this doesn't exist in sql 2000 here is the part of sql that store :

DECLARE @SQL AS [nvarchar](4000)     
            DECLARE @SumSQL AS [nvarchar](4000)       
            SET @SumSQL = ''     
            SET @SQL = ' ALTER TABLE [#t] ADD [TotalRemainingPrice] [FLOAT], [TotalRemainingQnt] [FLOAT], [MatUnitName] [nvarchar](256) COLLATE ARABIC_CI_AI, [AvgQty] [FLOAT], [AvgPrice] [FLOAT], [Price] [FLOAT]'     
            WHILE @PeriodCounter < @NumOfPeriods      
            BEGIN       
                SET @SQL = @SQL + ', [P' + CAST((@PeriodCounter+1) AS [nvarchar](10)) + '] [FLOAT]'        
                SET @SQL = @SQL + ', [r' + CAST((@PeriodCounter+1) AS [nvarchar](10)) + '] [FLOAT]'        

                SET @PeriodStart = @PeriodCounter * @PeriodLength      
                SET @PeriodEnd = @PeriodStart + @PeriodLength      

                IF @PeriodCounter = (@NumOfPeriods - 1) 
                        SET @SumSQL = @SumSQL +  ', ISNULL((SELECT SUM( [Remaining]) FROM [#In_Result] [t_inner] WHERE  [t_inner].[MatID] = [t_outer].[MatID] AND [t_inner].[Age] >' + CAST(@PeriodStart AS [nvarchar](10)) + '), 0)'      
                                    +    
                                    ', ISNULL((SELECT SUM( [Price] * [Remaining]) FROM [#In_Result] [t_inner] WHERE [t_inner].[MatID] = [t_outer].[MatID] AND [t_inner].[Age] > ' + CAST(@PeriodStart AS [nvarchar](10)) + '), 0)'       


                ELSE      
                BEGIN      
                    IF @PeriodCounter = 0    

                        SET @SumSQL = @SumSQL +  ', ISNULL((SELECT SUM([Remaining]) FROM [#In_Result] [t_inner] WHERE [t_inner].[MatID] = [t_outer].[MatID] AND ( [t_inner].[Age] = 0 OR ( [t_inner].[Age] > ' + CAST(@PeriodStart AS [nvarchar](10))       
                                    + ' AND [t_inner].[Age] <= ' + CAST(@PeriodEnd AS [nvarchar](10)) + '))), 0)'      
                                    +  
                                    ', ISNULL((SELECT SUM([Price] * [Remaining]) FROM [#In_Result] [t_inner] '      
                                    + ' WHERE [t_inner].[MatID] = [t_outer].[MatID]'      
                                    + ' AND ( [t_inner].[Age] = 0 OR ( [t_inner].[Age] > ' + CAST(@PeriodStart AS [nvarchar](10))       
                                    + ' AND [t_inner].[Age] <= ' + CAST(@PeriodEnd AS [nvarchar](10)) + '))), 0)'      


                    ELSE  

                        SET @SumSQL = @SumSQL +  ' , ISNULL((SELECT SUM([Remaining]) FROM [#In_Result] [t_inner] WHERE [t_inner].[MatID] = [t_outer].[MatID] AND [t_inner].[Age] > ' + CAST(@PeriodStart AS [nvarchar](10)) + ' AND t_inner.Age <= ' + CAST(@PeriodEnd AS [nvarchar](10)) + '), 0)'      
                        + ' , ISNULL((SELECT SUM([Price] * [Remaining]) FROM [#In_Result] [t_inner] WHERE [t_inner].[MatID] = [t_outer].[MatID] AND [t_inner].[Age] > ' + CAST(@PeriodStart AS [nvarchar](10)) + ' AND [t_inner].[Age] <= ' + CAST(@PeriodEnd AS [nvarchar](10)) + '), 0)'      

                END       
                SET @PeriodCounter = @PeriodCounter + 1     
            END 
            EXEC( @SQL) 
            DECLARE @SqlInsert AS [nvarchar](4000)
            DECLARE @SqlInsert2 AS [nvarchar](4000)
            SET @SqlInsert =  ' INSERT INTO [#t] SELECT [MatID], SUM([Price] * [Remaining]), SUM([Remaining]), [MatUnitName], [AvgQty], [AvgPrice], [Price1] ' --+ @SumSQL + 
            SET @SqlInsert2 =  ' FROM [#In_Result] [t_outer] GROUP BY [MatID], [MatUnitName],[AvgQty], [AvgPrice],[Price1]'     
            EXEC( @SqlInsert+@SumSQL+@SqlInsert2)  <--- here is where to execute stored nvarchar ! 

so what is the best way to store the nvarchar larger than 4000 in sql 2000 ?

Answer 1

If you really must use 2000 version, your best option would be to change the data type to from nvarchar(4000) to nText.
If it's even remotely possible, you should pressure your boss / customer to upgrade to a newer version of sql server.