STUFF function truncated result

I am trying to generate dynamic sql by querying a table and using the STUFF function and returning the results into a variable (DECLARE @dynamic_query NVARCHAR(max)).

My problem is that the results returned by the STUFF function are truncated/incomplete. The content of @dynamic_query will be cut short. I can’t find any references to character limits on the STUFF function online so I don’t know why this is happening.

I’ve tried to demonstrate the problem in this fiddle: http://sqlfiddle.com/#!18/e160f2/1/0 , however, I can’t recreate it in the fiddle and can’t use T-SQL variables. However, hopefully it gives you a rough idea of what I’m trying to do.

SELECT STUFF((
          SELECT 'CASE WHEN (long complicated ' + text_value + 'subqueries) THEN 0 ELSE 1, '
                  + 'WHEN (more long subqueries I dont want to type blahblahblahblah)'
          FROM source_table
          FOR XML PATH('')
        ), 1, 0, '')

Imagine that the results are cut short and the last characters are something like “WHEN (more lo“.

I thought it might be because the variable I’m returning the results into is too small but it is definitely NVARCHAR(max).

Any idea why this is happening?

Answer

It was because SSMS wasn’t returning all of the results, as comments below my post explain. Solution was to Save Results As a text file and then read results from there.

Advertisement

Answer