Я пытаюсь выполнить sql, используя @SumSQL var type nvarchar 4000, когда я ввожу значение параметра в моей хранимой процедуре больше определенного значения, такого как «11», программа возвращает ошибку sql... проблема возникает, когда sql сохраните char в var @SumSQL, его размер станет больше 4000, после чего он ничего не сохранит, в sql 2008, если я изменил тип nvarchar (4000) на nvarchar (max), он отлично работает, но этого не существует в sql 2000 вот часть sql, которая хранит:
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 !
так как лучше всего хранить nvarchar больше 4000 в sql 2000?
