Unable to Create the Complete Dynamic Query using NVARCHAR(Max)

Question

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)

Answer 1

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.

https://dba.stackexchange.com/questions/18483/varcharmax-field-cutting-off-data-after-8000-characters-sql-server-2008

Updated I show your entire code and want to explain some things.

1. 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)

2. 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 

Answer 2

• Make sure your variable don't contain quotes or double quote them.

  declare @value nvarchar(100) = 'abcd''efgh'
  declare @sql nvarchar(max)
  
  -- tons of QUOTE !!!
  Set @sql = N'select ''' + REPLACE(@value, '''', '''''') + '''';
  print @sql 
  exec sp_executesql @sql
  

• Put brackets around your object names and at least one space between into and DB:

  INSERT INTO'+ @AreaSchema+'.[SN_PLANNING_FGAllocation]
  -- space is missing
  

  or you can use the QUOTENAME function QUOTENAME(@AreaSchema)

• Use N'' and NVARCHAR(...) or '' and VARCHAR() but don't mix

You declare everything as NVARCHAR and then concatenate it with ''. It should be N''. You also convert/cast everything as VARCHAR. It should be NVARCHAR(...). Dynamic SQL use a NVARCHAR string.

• Make sure variable are set or have a default value

  SET @BucketId            = '+ CAST(@BucketId AS nVARCHAR) + '
  

This will return NULL because @BucketId is not set in your stored procedure. Either use a ISNULL, set it or give it a default value

• You should use EXEC sp_executesql and declare variables when they are used as such https://msdn.microsoft.com/fr-fr/library/ms188001(v=sql.120).aspx

  EXEC sp_executesql @yourquery, N'@UserID int, ...', @userID = @UserId ...
  

Keep concatenation only when it is needed (dynamic object names)

• Finally

Before trying to execute it, make sure your print output the correct query (print only show the 1st 4000 characters). If the query is incomplete, it probably means that your have an error in the code near the line where it stop. I tried your proc and it returns nothing (NULL) the way it is unless I first init all the variables. There are also truncations because you do not use N''