ayilmaz ayilmaz - 6 months ago 24
SQL Question

cannot store 5000 chars in NVARCHAR and VARCHAR

I have to create a dynamic sql query in SP. the dynamic query is 5000 chars, and I used NVARCHAR(MAX),VARCHAR(MAX), NVARCHAR(6000), VARCHAR(6000) but they all truncate to 4000 chars

DECLARE @SCRIPT VARCHAR(8000)
set @SCRIPT =' ASDADASD ASDA DSADAD AD AS D......' +@VAR1+ ' AWDAWd' -- 6000 CHARS
PRINT LEN(@SCRIPT) -- THIS PRINTS "4000"
PRINT @SCRIPT -- THIS PRINTS ONLY 4000 CHARS


How can increase the size of STRING? I have read many post but I couldnt get it done. Please help me?

Answer

Use the nvarchar(max) data type instead. Be sure to prefix string literals with the N designator (N' ASDADASD...'). Cast explicitely so the default limitation will not apply:

set @script=convert(nvarchar(max),N'very-long-literal')

(reference: The weird length of varchar and nvarchar in T-SQL)

EDIT: full working example:

DECLARE @SCRIPT nvarchar(max)
SET @SCRIPT=convert(nvarchar(max),N' ASDADASD ASDA  DSADAD AD AS D......')+replicate(convert(nvarchar(max),N'A'),6000)
PRINT LEN(@SCRIPT) -- this prints "6036"
PRINT @SCRIPT -- this prints all 6036 chars of the string
Comments