Search code examples
sqlsql-serverdynamic-sql

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?

Solution

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