Period getting truncated when print dynamic sql

Sudip Bhatt 2,246 Reputation points

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,          
 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,          
 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   
No comments
{count} votes

Accepted answer
  1. Viorel 88,571 Reputation points

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

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,611 Reputation points

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

  2. Erland Sommarskog 72,341 Reputation points MVP

    You can look here for some more tips on how to display long SQL strings: