Print a nvarchar variable

  • Comments posted to this topic are about the item Print a nvarchar variable

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • This was removed by the editor as SPAM

  • "however, you have to append in increments with each increment less than or equal to 4000"

    Not true.

    declare @test-2 nvarchar(max) = ''

    select @test-2 = @test-2 + N'x' + cast(space(6000) as nvarchar(max)) + N'x'

    select len(@test)

    select @test-2 = @test-2 + N'x' + cast(space(6000) as nvarchar(max)) + N'x'

    select len(@test)

    returns 6002 and 12004

  • Toreador (8/11/2015)


    "however, you have to append in increments with each increment less than or equal to 4000"

    Not true.

    +1

    Not sure where that assumption is coming from. Seems incorrect to me too.

  • Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • DECLARE @SQL NVARCHAR(MAX)= '';

    WITH Tally ( n )

    AS (

    -- 1000 rows of tally table

    SELECT

    ROW_NUMBER() OVER ( ORDER BY ( SELECT

    NULL

    ) )

    FROM

    ( VALUES ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0),

    ( 0) ) a ( n )

    CROSS JOIN ( VALUES ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0),

    ( 0), ( 0), ( 0) ) b ( n )

    CROSS JOIN ( VALUES ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0),

    ( 0), ( 0), ( 0) ) c ( n )

    )

    SELECT

    @SQL = @SQL + ', CONVERT(VARCHAR(10), CAST(' + CONVERT(NVARCHAR(10), n)

    + ' AS INT )) as ABC'

    FROM

    Tally;

    -- I am trying to append 48,893 (random number >4000) string length to variable @SQL

    SELECT

    LEN(@SQL) AS StringLength;

    PRINT @SQL; -- check your messages tab

  • The result I get when I ran all the code is 48893

  • Nice question, but it's a pity about the incorrect statement about only adding chunks less than 8000 bytes in the explanation.

    Tom

  • g.maxfield (8/11/2015)


    The result I get when I ran all the code is 48893

    +1 Same here, trying to figure out where is the mistake exactly.

  • And for those of you who are wondering why LEN(@SQL) is 48,893 it's because

    LEN(', CONVERT(VARCHAR(10), CAST(1 AS INT )) as ABC') is 47 and there are 9 single-digit numbers (1 through 9)

    LEN(', CONVERT(VARCHAR(10), CAST(10 AS INT )) as ABC') is 48 and there are 90 two-digit numbers (10 through 99)

    LEN(', CONVERT(VARCHAR(10), CAST(100 AS INT )) as ABC') is 49 and there are 900 three-digit numbers (100 through 999)

    LEN(', CONVERT(VARCHAR(10), CAST(1000 AS INT )) as ABC') is 50 and there is 1 four-digit number (1000)

    47 * 9 = 423

    48 * 90 = 4320

    49 * 900 = 44100

    50 * 1 = 50

    and 423 + 4320 + 44100 + 50 = 48893

  • Good question, different than normal, made me read the answers carefully and illustrates a good point. Thanks.

  • I ran into this recently while trying to generate a large number of stored procedures with a common format. Simple workaround:

    DECLARE @Offset INT = 1;

    WHILE LEN(@SQL) >= @Offset

    BEGIN

    PRINT SUBSTRING(@SQL,@Offset,4000);

    SET @Offset = @Offset + 4000;

    END

    Then clean up any extraneous line breaks.

  • I seem to recall that the construct:

    SELECT @var = @var + ...

    is not officially supported and may not always work. Or, am I misremembering something I read long ago?

    Found it!

    SET @local_variable (Transact-SQL)

    "Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur."

    and

    Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

    "The correct behavior for an aggregate concatenation query is undefined. "

    Gerald Britton, Pluralsight courses

  • g.maxfield (8/11/2015)


    DECLARE @SQL NVARCHAR(MAX)= '';

    WITH Tally ( n )

    AS (

    -- 1000 rows of tally table

    SELECT

    ROW_NUMBER() OVER ( ORDER BY ( SELECT

    NULL

    ) )

    FROM

    ( VALUES ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0),

    ( 0) ) a ( n )

    CROSS JOIN ( VALUES ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0),

    ( 0), ( 0), ( 0) ) b ( n )

    CROSS JOIN ( VALUES ( 0), ( 0), ( 0), ( 0), ( 0), ( 0), ( 0),

    ( 0), ( 0), ( 0) ) c ( n )

    )

    SELECT

    @SQL = @SQL + ', CONVERT(VARCHAR(10), CAST(' + CONVERT(NVARCHAR(10), n)

    + ' AS INT )) as ABC'

    FROM

    Tally;

    -- I am trying to append 48,893 (random number >4000) string length to variable @SQL

    SELECT

    LEN(@SQL) AS StringLength;

    PRINT @SQL; -- check your messages tab

    Yes the string length in the messages tab is 4000 characters. And the variable length is 48,893. Are you seeing something different in the messages tab?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Very well thought through - nice question. Thanks, Naveen!

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply