Period getting truncated when print dynamic sql

Sudip Bhatt 2,271 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   
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,545 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 111.5K 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).

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 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 100.8K Reputation points MVP

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

    0 comments No comments