I am using NVARCHAR(MAX) to create a dynamic query.Since NVARCHAR uses 2 bytes per character, Approximately 1 billion characters can include in NVARCHAR(MAX) variable (Link Reference) .
I tried by executing stored procedure in SQL Server itself, then executing the stored procedure through the application.
Both situation dynamic query is not exceeding those character length. But only part of the Dynamic query is get executed.Because of that stored procedure throw errors to the application.
Am I missing any code ?
USE [MyDemoDB]
GO
ALTER PROCEDURE [dbo].[sp_Apply]
(
@scenarioId INT,
@userId INT,
@bookId INT
)
AS
DECLARE @BucketId INT
DECLARE @HierarchyId NVARCHAR(10)
DECLARE @Year INT
DECLARE @Month INT
DECLARE @PlanningSeason NVARCHAR(20)
DECLARE @StructureId INT = 9
DECLARE @AllocStructureId INT = 11
DECLARE @UpdatedUser INT = 2
DECLARE @InsertOne NVARCHAR(MAX)=''
DECLARE @AreaSchema NVARCHAR(40)
DECLARE @AreaCode NVARCHAR(20)
DECLARE @EmptyValue NVARCHAR(20)
SET @AreaCode = ''
SET @AreaSchema = '[dbo]'
SET @InsertOne = '
DECLARE @FGSupplySeqId INT
DECLARE @FGSupplyId NVARCHAR(10)
DECLARE @PlannedQty DECIMAL(18,2)
DECLARE @ConfirmdQty DECIMAL(18,2)
DECLARE @Year INT
DECLARE @Month INT
DECLARE @Season NVARCHAR(20)
DECLARE @MerchantId NVARCHAR(50)
DECLARE @UpdatedUser INT
DECLARE @HierarchyId NVARCHAR(10)
DECLARE @BucketId INT
DECLARE @ProductNo NVARCHAR(100)
DECLARE @LocationNo NVARCHAR(100)
SET @BucketId = '+ CAST(@BucketId AS VARCHAR) + '
SET @UpdatedUser = '+ CAST(@userId AS VARCHAR) + '
IF @BucketId = 1
BEGIN
DECLARE Supplys
CURSOR FOR
SELECT [FGSupplySeqId],[FGSupplyId] FROM ' + @AreaSchema + '.[FGSupply]
WHERE PlanningScenarioId ='+ CONVERT(VARCHAR(10),@scenarioId)+ '
OPEN Supplys
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE Allocations
CURSOR FOR
SELECT @FGSupplyId,FGHierarchyId,MerchantNo,PlannedQty,ConfirmedQty,Year,Season,ProductNo,LocationNo
FROM '+ @AreaSchema +'.[FGAllocation]
WHERE FGSupplySeqId = @FGSupplySeqId
OPEN Allocations
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Season,@ProductNo,@LocationNo
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FGAllocationId NVARCHAR(10)
DECLARE @AllocStatus INT
SET @FGAllocationId = ''E''
SET @AllocStatus= 0
SELECT @FGAllocationId = FGAllocationId,@AllocStatus=Status
FROM ' + @AreaSchema+'.[SN_PLANNING_FGAllocation]
WHERE [HierarchyId]=@HierarchyId AND [MerchantNo]=@MerchantId AND YEAR = @Year AND [Month] IS NULL
IF @FGAllocationId = ''E''
BEGIN
-- IF @AllocStatus <> 5
INSERT INTO'+ @AreaSchema+'.[SN_PLANNING_FGAllocation]
(FinishedGoodSupplyId,FGHierarchyId,MerchantNo,PlannedQty,Year,Season,Status,IsActive,CreatedBy,UpdatedBy,CreatedOn,UpdatedOn,ProductNo,LocationNo)
VALUES(@FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@Year,@Season,0,1,@UpdatedUser,@UpdatedUser,GETDATE(),GETDATE(),@ProductNo,@LocationNo)
END
ELSE
BEGIN
-- IF @AllocStatus <> 5
UPDATE ' + @AreaSchema + '.[SN_PLANNING_FGAllocation]
SET PlannedQty = @PlannedQty ,ConfirmedQty=@ConfirmdQty,UpdatedBy=@UpdatedUser, UpdatedOn=GETDATE()
WHERE FGAllocationId = @FGAllocationId
END
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Season,@ProductNo,@LocationNo
END
CLOSE Allocations
DEALLOCATE Allocations
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
END
CLOSE Supplys
DEALLOCATE Supplys
END
IF @BucketId = 2
BEGIN
DECLARE Supplys
CURSOR FOR
SELECT [FGSupplySeqId],[FGSupplyId] FROM ' + @AreaSchema + '.[FGSupply]
WHERE PlanningScenarioId ='+ CONVERT(VARCHAR(10),@scenarioId)+ 'AND Month IS NOT NULL
OPEN Supplys
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE Allocations
CURSOR FOR
SELECT @FGSupplyId,FGHierarchyId,MerchantNo,PlannedQty,ConfirmedQty,Year, Month,Season,@ProductNo,@LocationNo
FROM '+ @AreaSchema +'.[FGAllocation]
WHERE FGSupplySeqId = @FGSupplySeqId AND Month IS NOT NULL
OPEN Allocations
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Month,@Season,@ProductNo,@LocationNo
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FGAllocationId1 NVARCHAR(10)
SET @FGAllocationId1 = ''E''
SELECT @FGAllocationId1 = FGAllocationId,@AllocStatus=Status
FROM ' + @AreaSchema+'.[SN_PLANNING_FGAllocation]
WHERE [HierarchyId]=@HierarchyId AND [MerchantNo]=@MerchantId AND YEAR = @Year AND [Month] = @Month
IF @FGAllocationId1 = ''E''
BEGIN
-- IF @AllocStatus <> 5
INSERT INTO'+ @AreaSchema+'.[SN_PLANNING_FGAllocation]
(FGSupplyId,FGHierarchyId,MerchantNo,PlannedQty,Year,Month,Season,Status,IsActive,CreatedBy,UpdatedBy,CreatedOn,UpdatedOn,ProductNo,LocationNo)
VALUES(@FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@Year,@Month,@Season,0,1,@UpdatedUser,@UpdatedUser,GETDATE(),GETDATE(),@ProductNo,@LocationNo)
END
ELSE
BEGIN
-- IF @AllocStatus <> 5
UPDATE ' + @AreaSchema + '.[SN_PLANNING_FGAllocation]
SET PlannedQty = @PlannedQty ,ConfirmedQty=@ConfirmdQty,UpdatedBy=@UpdatedUser, UpdatedOn=GETDATE()
WHERE FGAllocationId = @FGAllocationId1
END
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Month,@Season,@ProductNo,@LocationNo
END
CLOSE Allocations
DEALLOCATE Allocations
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
END
CLOSE Supplys
DEALLOCATE Supplys
END'
print @InsertOne
EXEC(@InsertOne)
Yes, you may be facing the issue because of nvarchar limit is 4000 characters.
I also face this issue and resolved by concatenate the string and then execute.
If you select or print it only show 4000 character, but if you concatenate or append the string it must append (till 8000 character). So don't bother about this, you do not print or select just append and execute and its definitely work.
In this link this is explain.
declare @sql Nvarchar(max),
@a nvarchar(max),
@b nvarchar(max);
select @sql =N'', @a = N'a', @b = N'b';
select @sql = @sql +replicate(@a,4000) + replicate(@b, 6000);
select len(@sql)
There is one rule for this :-
SET @dynamicSQL = [concatenate various unicode strings and nvarchar variables totalling over 4000 characters] -- MAKE SURE AT LEAST ONE OF THE UNICODE STRINGS IS NVARCHAR(MAX), YOU CAN CAST ANY ONE ARGUMENT.
You can check this link also.
Updated I show your entire code and want to explain some things.
First of all, why you want dynamic query. Code shows that you can do it without dynamic query and also there is so much nested cursor (try to ignore it with simple query)
Still if you want to go, then I remove you extra code (I don't think that remove space will work, I had 4 union query and its very huge length and its work with this strategy after verify each part in separate window)
a.Here is another option before you read below. rather than define parameter in query, you can pass this parameter too.
begin tran
create table table1 ( id int, value varchar(10) )
insert into table1 values( 1,'001')
insert into table1 values(2, '002')
insert into table1 values( 3,'003')
insert into table1 values( 4,'004')
declare @sql nvarchar(max) , @temp nvarchar(50) = '1,2,3', @tempIntSingleValue nvarchar(50) = '2'
select * from table1
set @sql = 'select * from table1 where id in ( ' + @temp + ')'
print @sql
exec sp_executesql @sql
set @sql = 'select * from table1 where id in ( @tempInner)'
print @sql
exec sp_executesql @sql , N'@tempInner int', @tempInner = @tempIntSingleValue
rollback
b. you used same parameter in your dynamic query. so I think issue with you have to give either default value or assign value at run-time. so while concatenating your string not become null. See this example below. I am define all character to '' and int to numeric value and at last print which print something. If we not define it never print blank due to concatenate set null value.
declare @scenarioId INT = 1 ,
@userId INT = 5,
@bookId INT = 1
DECLARE @BucketId INT = 0
DECLARE @HierarchyId NVARCHAR(10)
DECLARE @Year INT
DECLARE @Month INT
DECLARE @PlanningSeason NVARCHAR(20)
DECLARE @StructureId INT = 9
DECLARE @AllocStructureId INT = 11
DECLARE @UpdatedUser INT = 2
DECLARE @InsertOne NVARCHAR(MAX) =''
DECLARE @AreaSchema NVARCHAR(40)
DECLARE @AreaCode NVARCHAR(20)
DECLARE @EmptyValue NVARCHAR(20)
SET @AreaCode = ''
SET @AreaSchema = '[dbo]'
SET @InsertOne =
'DECLARE @FGSupplySeqId INT = 5
DECLARE @FGSupplyId NVARCHAR(10) = ''''
DECLARE @PlannedQty DECIMAL(18,2) = ''''
DECLARE @ConfirmdQty DECIMAL(18,2) = ''''
DECLARE @Year INT = 2015
DECLARE @Month INT = 7
DECLARE @Season NVARCHAR(20) = ''''
DECLARE @MerchantId NVARCHAR(50) = ''''
DECLARE @UpdatedUser INT
DECLARE @HierarchyId NVARCHAR(10) = ''''
DECLARE @BucketId INT = 0
DECLARE @ProductNo NVARCHAR(100)= ''''
DECLARE @LocationNo NVARCHAR(100)
SET @BucketId = '+ CAST(@BucketId AS VARCHAR) + '
SET @UpdatedUser = '+ CAST(@userId AS VARCHAR) + '
IF @BucketId = 1
BEGIN
DECLARE Supplys
CURSOR FOR
SELECT [FGSupplySeqId],[FGSupplyId] FROM ' + @AreaSchema + '.[FGSupply]
WHERE PlanningScenarioId ='+ CONVERT(VARCHAR(10),@scenarioId)+ '
OPEN Supplys
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE Allocations
CURSOR FOR
SELECT @FGSupplyId,FGHierarchyId,MerchantNo,PlannedQty,ConfirmedQty,Year,Season,ProductNo,LocationNo
FROM '+ @AreaSchema +'.[FGAllocation]
WHERE FGSupplySeqId = @FGSupplySeqId
OPEN Allocations
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Season,@ProductNo,@LocationNo
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FGAllocationId NVARCHAR(10)
DECLARE @AllocStatus INT
SET @FGAllocationId = ''E''
SET @AllocStatus= 0
SELECT @FGAllocationId = FGAllocationId,@AllocStatus=Status
FROM ' + @AreaSchema+'.[SN_PLANNING_FGAllocation]
WHERE [HierarchyId]=@HierarchyId AND [MerchantNo]=@MerchantId AND YEAR = @Year AND [Month] IS NULL
IF @FGAllocationId = ''E''
BEGIN
-- IF @AllocStatus <> 5
INSERT INTO'+ @AreaSchema+'.[SN_PLANNING_FGAllocation]
(FinishedGoodSupplyId,FGHierarchyId,MerchantNo,PlannedQty,Year,Season,Status,IsActive,CreatedBy,UpdatedBy,CreatedOn,UpdatedOn,ProductNo,LocationNo)
VALUES(@FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@Year,@Season,0,1,@UpdatedUser,@UpdatedUser,GETDATE(),GETDATE(),@ProductNo,@LocationNo)
END
ELSE
BEGIN
-- IF @AllocStatus <> 5
UPDATE ' + @AreaSchema + '.[SN_PLANNING_FGAllocation]
SET PlannedQty = @PlannedQty ,ConfirmedQty=@ConfirmdQty,UpdatedBy=@UpdatedUser, UpdatedOn=GETDATE()
WHERE FGAllocationId = @FGAllocationId
END
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Season,@ProductNo,@LocationNo
END
CLOSE Allocations
DEALLOCATE Allocations
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
END
CLOSE Supplys
DEALLOCATE Supplys
END
IF @BucketId = 2
BEGIN
DECLARE Supplys
CURSOR FOR
SELECT [FGSupplySeqId],[FGSupplyId] FROM ' + @AreaSchema + '.[FGSupply]
WHERE PlanningScenarioId ='+ CONVERT(VARCHAR(10),@scenarioId)+ 'AND Month IS NOT NULL
OPEN Supplys
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE Allocations
CURSOR FOR
SELECT @FGSupplyId,FGHierarchyId,MerchantNo,PlannedQty,ConfirmedQty,Year, Month,Season,@ProductNo,@LocationNo
FROM '+ @AreaSchema +'.[FGAllocation]
WHERE FGSupplySeqId = @FGSupplySeqId AND Month IS NOT NULL
OPEN Allocations
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Month,@Season,@ProductNo,@LocationNo
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FGAllocationId1 NVARCHAR(10)
SET @FGAllocationId1 = ''E''
SELECT @FGAllocationId1 = FGAllocationId,@AllocStatus=Status
FROM ' + @AreaSchema+'.[SN_PLANNING_FGAllocation]
WHERE [HierarchyId]=@HierarchyId AND [MerchantNo]=@MerchantId AND YEAR = @Year AND [Month] = @Month
IF @FGAllocationId1 = ''E''
BEGIN
-- IF @AllocStatus <> 5
INSERT INTO'+ @AreaSchema+'.[SN_PLANNING_FGAllocation]
(FGSupplyId,FGHierarchyId,MerchantNo,PlannedQty,Year,Month,Season,Status,IsActive,CreatedBy,UpdatedBy,CreatedOn,UpdatedOn,ProductNo,LocationNo)
VALUES(@FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@Year,@Month,@Season,0,1,@UpdatedUser,@UpdatedUser,GETDATE(),GETDATE(),@ProductNo,@LocationNo)
END
ELSE
BEGIN
-- IF @AllocStatus <> 5
UPDATE ' + @AreaSchema + '.[SN_PLANNING_FGAllocation]
SET PlannedQty = @PlannedQty ,ConfirmedQty=@ConfirmdQty,UpdatedBy=@UpdatedUser, UpdatedOn=GETDATE()
WHERE FGAllocationId = @FGAllocationId1
END
FETCH NEXT
FROM Allocations
INTO @FGSupplyId,@HierarchyId,@MerchantId,@PlannedQty,@ConfirmdQty,@Year,@Month,@Season,@ProductNo,@LocationNo
END
CLOSE Allocations
DEALLOCATE Allocations
FETCH NEXT
FROM Supplys
INTO @FGSupplySeqId,@FGSupplyId
END
CLOSE Supplys
DEALLOCATE Supplys
END'
print @InsertOne