Print a nvarchar variable

Question

Post reply

Print a nvarchar variable

Naidu PK

SSCrazy

Points: 2481
More actions
August 10, 2015 at 11:12 pm

#299546

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
August 11, 2015 at 12:46 am

This was removed by the editor as SPAM

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

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

Toreador SSChampion Points: 11358 More actions · Answer 1

"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

Mighty SSCrazy Eights Points: 9063 More actions · Answer 2

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.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Nice question, thanks.

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

g.maxfield Default port Points: 1471 More actions · Answer 4

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

g.maxfield Default port Points: 1471 More actions · Answer 5

g.maxfield

Default port

Points: 1471

August 11, 2015 at 4:00 am

#1818840

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

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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

Tom

Hany Helmy SSChampion Points: 13921 More actions · Answer 7

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.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

briankwartler Ten Centuries Points: 1332 More actions · Answer 8

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 9

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sknox SSChampion Points: 12388 More actions · Answer 10

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.

g.britton SSChampion Points: 13863 More actions · Answer 11

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

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

Revenant SSC-Forever Points: 42467 More actions · Answer 13

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