Period getting truncated when print dynamic sql

Sudip Bhatt 2,271 Reputation points
2020-12-11T17:37:49.437+00:00

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   
Transact-SQL
Transact-SQL
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
    2020-12-11T20:09:46.557+00:00

    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
    2020-12-11T18:31:11.053+00:00

    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


  2. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-11T22:38:10.127+00:00

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

    0 comments No comments