Bug With SQL Insert in SP over 8000 varchars

Charles Teague 0 Reputation points
2024-06-11T14:53:10.29+00:00

How do I report a bug with SQL Server? Here is the bug.

Create an insert statement that contains a value with more then 8000 chars for a varchar(max) field. Run the insert statement and see that it works and you receive the entire result back in the grid. This means you have the proper configurations. This also means the SQL Server behaved correctly, changing to text.

Write a stored procedure with the same insert statement. Execute that stored procedure.

Results: only 8000 chars are in that field.

Expected Results: The entire value is in the field, just as before.

Work-around:
In the stored procedure, set variable = to less that the first 8000 chars, and append that variable with the rest (assuming there are less than 8000 more). Put that variable in the insert statement in the stored procedure. That will work as expected. The field will have the entire value even though it is greater than 8000 chars.

I did this in the latest version of SQL Server management studio: 20.1.10.0.
sql-version

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,274 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 106K Reputation points MVP
    2024-06-11T21:17:57.1466667+00:00

    To report a bug, you need to have a script or some other means to reproduce the bug. Once you have this, you can report it on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0.

    However, don't do that. From what you say, there is no reason to assume that there is a bug, but you are victim to a trap in the SQL Server type system.

    Consider this:

    DECLARE @a varchar(8000) = replicate('X', 5000),
            @b varchar(8000) = replicate('X', 5000),
            @max varchar(MAX)
    SELECT @max = @a + @b
    SELECT datalength(@max)
    
    
    

    A naïve assumption is that the final SELECT will return 10000, but that assumption is not correct. Generally, when an expression is an operation of operands with the same data type, the expression assumes that data type as well. So if you have a concatenation of two limited varchar value, the result will also be a limited varchar value. The fact you assign the result to varchar(MAX), a different data type, is of no importance, since the expression is evaluated before assignment occurs.

    For the return type of the expression to be varchar(MAX), at least one operand needs to be of this data type. This returns 10000

    DECLARE @a varchar(8000) = replicate('X', 5000),
            @b varchar(8000) = replicate('X', 5000),
            @max varchar(MAX)
    SELECT @max = convert(varchar(MAX), @a) + @b
    SELECT datalength(@max)
    
    1 person found this answer helpful.

  2. Bruce (SqlWork.com) 60,871 Reputation points
    2024-06-11T22:30:52.6866667+00:00

    the bug is probably in your code. if you show the code we can explain the bug. somewhere in your code, you are passing the value as an implicit varchar() which has a max length of 8000.


  3. Bruce (SqlWork.com) 60,871 Reputation points
    2024-06-12T15:51:52.28+00:00

    Now that we can see your code, we can see your bug. You are using implicit conversion of literals. Implicit type of an expression is based on the datatype of the variables in the expression. In your case the the literal variables are varchar() not varchar(max) as they are under 8k. you should cast the literals and converts to varchar(max)

    insert into ShowMe (Message)
    Values (
       cast('757....' as varchar(max)) 
       + cast(@aNumber as varchar(max)) 
       + cast('1234...' as varchar(max))
    );
    
    

    note: varchar(max) is a different datatype then varchar(<number>)

    0 comments No comments