Period getting truncated when print dynamic sql

Question

When i am Printing the sql then @PeriodAlias getting truncated. not all the period is coming in printed sql. please guide me what to change as a result dynamic column should not be truncated. thanks

 Declare @PeriodAliasComment NVARCHAR(MAX)          
 SET @sql = N'          
 Update #TmpBM Set BM_Element=Output.BM_Element,          
 '+@UpdateSql+'           
 From #TmpBM tmp,           
 (Select Section,LineItem,BM_Code Code,(Case When isnull(a.BM_Element,'''')='''' then b.bm_element else a.BM_Element end) BM_Element,          
    '+@PeriodAlias+'           
 From tblWebCSM a INNER JOIN tblBlueMetricsMaster b ON a.BM_code=b.code           
 Where Ticker='''+@Ticker +''' And Isnull(BM_Code,'''')!='''' And Isnull(BM_Code,'''') In (''01'',''07'',''10'')     
  ) Output          
  Where tmp.Section=Output.Section And tmp.LineItem=Output.LineItem And tmp.Code=Output.Code   

Answer 1

If you want to display long strings for debugging purposes, then maybe create a helper procedure, which will include a loop like this:

declare @s nvarchar(max) = @PeriodAlias
while datalength(@s) > 0 begin print left(@s, 100) set @s = substring(@s, 100, len(@s)) end

The text will be wrapped. You can replace 100 with larger widths (less than 4000).

Answer 2

The PRINT command only prints the first 4000 characters. If your command is larger than 4000 characters, it will not work.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/print-transact-sql?view=sql-server-ver15#remarks

Answer 3

You can look here for some more tips on how to display long SQL strings:
http://www.sommarskog.se/dynamic_sql.html#debugprint