Html Question posted in
Some basic documentation can be found here.

Html – Dynamic SQL query being truncated to the length of one value

I have a SQL query in which I’m returning a string containing an HTML <tr> for each row returned by my query, see foot of this message for an example.

As you can see in my example below, one ‘column’, if you will, is being converted into an nvarchar(4000). What I would expect this to do, perhaps naively, is convert the data in that column into an nvarchar(4000) but to then append to that all the other columns and '<td>' etc, such that the overall length of the string returned is longer than 4000 characters. However, what’s happening is that the entire string being returned, i.e. '<tr>' + ... + '</tr>' is limited to 4000 characters in length. In some instances, the data in just one column is so long that the entire string exceeds 4000 characters and thus gets cut off, for example:

<tr><td>12345678-01-01</td><td>12345678</td><td>APPLE</td><td>Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</td><td>03/04/2018 Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla par.</td><td>03/04/2018</t

As you can see, the string returned ends in '</t', which is incomplete HTML and thus causes issues when trying the pass this HTML to javascript to do something with it.

It’s worth mentioning before anyone tries to answer, but for really boring reasons that I won’t get into here, I CAN’T use nvarchar(max) in this circumstance, the best I can do is nvarchar(4000). Why does this behaviour force the entire string returned to be an nvarchar(4000) as opposed to truncating the value within the specific column and then simply appending the rest of the string to that, such that it can exceed 4000 chars? I.e. I absolutely get the fact that a column’s data type might be nvarchar(4000), but If I concatenated two such columns together I’d expect to be able to do so without truncation.

Is there any way I can get round this without using nvarchar(max)? Also, whilst I absolutely appreciate suggestions that I could build this using technologies other than SQL, let’s just assume for the purposes of this question that SQL server is my only option.

    select
    '<tr>'
        + '<td>' + isnull(stu.stu_code,'') + '</td>'
        + '<td>' + isnull(sta.sta_name,'') + '</td>'
        + '<td>' + replace(replace(
            case 
                when isnull(convert(nvarchar(4000),ccl.ccl_note),'') = ''
                then isnull(convert(nvarchar(4000),scc.scc_note),'')
                else isnull(convert(nvarchar(4000),ccl.ccl_note),'')
            end, char(10),''),char(13),'')  
        + '</td>'
        + '<td>' + case when scc.scc_cred is null then '' else format(scc.scc_cred,'dd/MM/yyyy') end + '</td>' 
+ '</tr>'

from ...
Tags:

2

Answers


  1. 0

    Just to expand on my comment … Note the null‘s

    Select td = col1
      ,null
      ,td = col2
      ,null
      ,td = col3
 From YourTable
 For XML Path('tr')
    Login or Signup to reply.
  2. 0

    It sounds like you actually do want nvarchar(max), but you want to truncate each column to 4000.

    So use LEFT

    select
    '<tr>'
        + '<td>' + isnull(stu.stu_code,'') + '</td>'
        + '<td>' + isnull(sta.sta_name,'') + '</td>'
        + '<td>' +
         convert(nvarchar(max),
           left(
             replace(replace(
               isnull(
                 nullif(ccl.ccl_note, ''),
                 nullif(scc.scc_note, '')
               ),
               nchar(10), ''), nchar(13), ''
             ),
             4000
           )
         )
        + '</td>'
        + '<td>' + isnull(convert(nvarchar(max), scc.scc_cred, 103), '') + '</td>' 
+ '</tr>'

from etc

    I agree @JohnCappelleti that you should instead use FOR XML. A slightly different version:

    select
  (
    select
      td
    from (values
      (convert(nvarchar(max), isnull(stu.stu_code,''))),
      (isnull(sta.sta_name,'')),
      (
       left(
         replace(replace(
           isnull(
             nullif(ccl.ccl_note, ''),
             nullif(scc.scc_note, '')
           ),
           nchar(10), ''), nchar(13), ''
         ),
         4000
       ),
      ),
      (isnull(convert(nvarchar(max), scc.scc_cred, 103), ''))
    ) as v(td)
    for xml path('tr')
  )
from etc
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search