Why NVarchar(MAX) cannot execute complete dynamic query using SP_ExecuteSQL?

Question

I created a stored procedure that will have two queries, those two queries will update records in two tables.

So I will mention what it shows

My procedure is like below

ALTER PROC UpdateMissingItemONPoe 
    @StoreID INT,
    @Name VARCHAR(20)
AS
BEGIN
    DECLARE @UpdateRec NVARCHAR(MAX) = '';
    DECLARE @StoreIP SYSNAME = ''

    SET @StoreIP = CASE @StoreID 
                      WHEN 1111 THEN '[192.168.1.45].[Store1].[dbo]'
                      WHEN 2222 THEN '[192.168.2.45].[Store2].[dbo]'
                      WHEN 3333 THEN '[192.168.3.45].[Store3].[dbo]'
                   END
    SET @UpdateRec = N' IF((SELECT NameID from '+@StoreIP+'.Details WHERE Name = '''+@Name+''') = 2 )
BEGIN
    INSERT INTO ' + @StoreIP + '.PurchaseOrderEntry(
            /*  InsertField Name  */
        )

        select
            Name,
            Standard,
            Department,
            Category,
            SubDescription,
            Rank
        from
            '+@StoreIP+'.Details 
        where
            Name = '''+@Name+'''

        INSERT INTO '+@StoreIP+'.HeadQuarter(
            /*  InsertField Name  */
        )
        select
            Name,
            Standard,
            Department,
            Category,
            SubDescription,
            Rank
        from
            '+@StoreIP+'.Details 
        where
            Name = '''+@Name+'''
    END'

    print @UpdateRec
    EXEC sp_executesql @UpdateRec
END

When I execute this stored procedure, it is not showing any error message. But, that dynamic query is not having complete query when I print.

Printed query is like

INSERT INTO '+@StoreIP+'.PurchaseOrderEntry(
    /*  InsertField Name  */
)

select
    Name,
    Standard,
    Department,
    Category,
    SubDescription,
    Rank
from
    '+@StoreIP+'.Details 
where
    Name = '''+@Name+'''

INSERT INTO '+@StoreIP+'.HeadQuarter(
    /*  InsertField Name  */
)

select
    Name,
    Standard,
    Department,
    Category,

The remaining lines are missing

what is the length of the `@UpdateRec` ? `print @UpdateRec` might not be able to print out everything in `@UpdateRec`, it is subject to the settings in `SSMS` — Squirrel, Sep 22 '18 at 09:18
not the data type size. I mean the length of the formed query. do a `SELECT LEN(@UpdateRec)` or `datalength(@UpdateRec)` — Squirrel, Sep 22 '18 at 09:21
Can't reproduce on SQL Server 2016 - the print out properly shows (a) the `@StoreIP` being replaced with the correct expected value from your `CASE`, and the output contains **all** the lines..... — marc_s, Sep 22 '18 at 09:24
@Liamneesan, not only is the printed query truncated, the `IF` statement is missing and variables weren't concatenated properly. The code you posted will include that unconditionally and runs as expected for me. I suspect the proc you are actually executing is different than the one in your question. — Dan Guzman, Sep 22 '18 at 09:47
what is the values you are passing for StoreID and Name? It's very strange to see your output query still has '@StoreIP', '@Name' in it. Please give the output by executing the procedure — Akshay G, Sep 22 '18 at 10:09

score 3 · Answer 1 · answered Sep 22 '18 at 09:35

3

Your query definitely exceed the 4000 characters.

change the SET statement to

SET @UpdateRec = CONVERT(NVARCHAR(MAX), '') + N' IF(('

it will forced your query string N' IF ((... to nvarchar(max).

answered Sep 22 '18 at 09:35

Squirrel

23,507
4
34
32

I did it as you mentioned. Now the length it shows `4724`. but still it doesn't show full query – Liam neesan Sep 22 '18 at 09:49
`SSMS` is unable to print out full text length. I remember there is a max length but i can't remember. To print out the full query, you need to do `substring()` and print out `4000` at a time. But does your query executes ? – Squirrel Sep 22 '18 at 10:06
That query is working fine. Now I make two `NVarchar(MAX) variable` and execute those variable using `sp_executesql`. It executed successfully and the records are inserted on my table. I just tried on single variable to execute only at single time. it's k – Liam neesan Sep 22 '18 at 10:12
1

@Liamneesan Then please accept the response as the answer. That is how SO is designed to work; it helps when the asker of a question marks an answer so that others can find the answers. – SMor Sep 22 '18 at 10:26
@SMor Sorry, Nobody tell what is the problem with my query, they just gave alternate way for my solution. What's wrong with my query?... Even I found alternative way and I executed it successfully like they posted. – Liam neesan Sep 22 '18 at 10:36
@Liamneesan - if you want people to tell you the problem with your query you need to post a full example that actually reproduces the problem you are asking about. Possibly you are encountering an issue described here https://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits/12639972#12639972 – Martin Smith Sep 22 '18 at 12:24
@Liamneesan The problem with your query is that the type you declare for the final variable (`@UpdateRec NVARCHAR(MAX)`) does not determine how the calculation of the right side of the assignment is performed. It only affects what happens to the already calculated result when it is finally ready to be saved into the variable. In your query expression, you have many small pieces of text, each of which happens to have the type `(n)varchar(x)`, where `x` is a number and *not* `max`. This is because each of the pieces is short enough to fit into the maximum of `varchar(8000)` or `nvarchar(4000)`. – GSerg Sep 22 '18 at 23:58
@Liamneesan When all such pieces are glued together, the result is not going to magically become `(n)varchar(max)` - it's going to remain `(n)varchar(number)`, so the result of the gluing is going to be truncated at the `number`, and *only then* it will be stored in an `nvarchar(max)`. The solution is to make sure the expression has at least one piece in it that is of type `(n)varchar(max)`. Then all other pieces will be coerced to `(max)` *before* gluing, due to type precedence. Hence this answer is correct. – GSerg Sep 22 '18 at 23:59

Lukasz Szozda · Answer 2 · 2018-09-22T12:11:16.500

I would rewrite it as:

ALTER PROC UpdateMissingItemONPoe 
    @StoreID INT,
    @Name VARCHAR(20)
AS
BEGIN
    DECLARE @UpdateRec NVARCHAR(MAX) = N'';
    DECLARE @StoreIP NVARCHAR(128) = N'';

    SET @StoreIP = CASE @StoreID 
                      WHEN 1111 THEN '[192.168.1.45].[Store1].[dbo]'
                      WHEN 2222 THEN '[192.168.2.45].[Store2].[dbo]'
                      WHEN 3333 THEN '[192.168.3.45].[Store3].[dbo]'
                   END;

    SET @UpdateRec = N' IF((SELECT NameID from <@StoreIP>.Details 
                           WHERE Name = @Name) = 2 )
BEGIN
    INSERT INTO <StoreIP>.PurchaseOrderEntry(
            /*  InsertField Name  */
        )

        select
            Name,
            Standard,
            Department,
            Category,
            SubDescription,
            Rank
        from
            <@StoreIP>.Details 
        where
            Name = @Name

        INSERT INTO <@StoreIP>.HeadQuarter(
            /*  InsertField Name  */
        )
        select
            Name,
            Standard,
            Department,
            Category,
            SubDescription,
            Rank
        from
            <@StoreIP>.Details 
        where
            Name = @Name
    END';

   SET @UpdateRec = REPLACE(@UpadteRec, N'<@StoreIP>', @StoreIp);

    print @UpdateRec
    EXEC sp_executesql @UpdateRec, N'@Name NVARCHAR(20)', @Name;
END

Using parametrized dynamic SQL and exchanging table name using REPLACE instead of concatenation.

score 0 · Answer 3 · answered Sep 22 '18 at 23:25

In the past the SQL Server used to convert such multi part statements into nvarchar(4000) if these parts were not explicitly converted to the nvarchar(max) type.

So if you had a variable

DECLARE @UpdateRec NVARCHAR(MAX) = '';

Then used this type of syntax

SET @UpdateRec = N' IF((SELECT NameID from '+@StoreIP+'.Details WHERE
 Name = '''+@Name+''') = 2 ) BEGIN
 INSERT INTO ' + @StoreIP + '.PurchaseOrderEntry( (...)'

The result would be converted silently to nvarchar(4000) and would truncate any excess characters without warning.

Why NVarchar(MAX) cannot execute complete dynamic query using SP_ExecuteSQL?

3 Answers3