Dynamic SQL being truncated - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Dynamic SQL being truncated

Author

Topic

measterbro
Starting Member

2 Posts

Posted - 2015-03-12 : 08:51:43

When I append a string variable to dynamic SQL it truncates at 4K. If I don't append, it allows me to use the entire nvarchar(max) amount.
Any suggestions?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-12 : 09:00:18

yes, post your code.

Kristen
Test

22859 Posts

Posted - 2015-03-18 : 09:20:42

Do you mean


EXEC ('SOME CODE' + @MyParameter)

I think that may be CAST to NVARCHAR and thus max 4000 characters

where


DECLARE @MyParameter varchar(MAX)
...
SELECT @MyParameter = 'SOME CODE' + @MyParameter
EXEC (@MyParameter)

should be treated as a VARCHAR/NVARCHAR(MAX) variable

umertahir
Posting Yak Master

154 Posts

Posted - 2015-03-18 : 09:53:22

try this


DECLARE @SQL1 NVARCHAR(4000)
DECLARE @SQL2 NVARCHAR(4000)
DECLARE @SQL3 NVARCHAR(4000)


SELECT @SQL1 = 'INSERT INTO ........'
SELECT @SQL1 = 'SELECT Column1, ....'
SELECT @SQL2 = 'WHERE .......'


PRINT (@SQL1+@SQL2+@SQL3)
EXEC (@SQL1+@SQL2+@SQL3)

Kristen
Test

22859 Posts

Posted - 2015-03-18 : 11:46:33

quote:
Originally posted by umertahir

try this

Should no longer be necessary in recent versions of SQL:


DECLARE	@strSQL varchar(MAX)
SELECT @strSQL = 'PRINT ''START'''
		+ REPLICATE(' ', 4000)
		+ 'PRINT ''END'''
SELECT [Len] = LEN(@strSQL)
EXEC (@strSQL)

gives:


Len                  
-------------------- 
4024

(1 row(s) affected)

START
END

Kristen
Test

22859 Posts

Posted - 2015-03-18 : 11:49:06

Having said that string concatenation seems to work too:


EXEC ('PRINT ''PreSQL!''' + @strSQL)

Kristen
Test

22859 Posts

Posted - 2015-03-18 : 11:57:28

The 4000 limit is spookily like the limit on NVARCHAR(4000) that I wonder if there is some intermediary @Parameter / @Variable that is declared as NVARCHAR(4000) - rather than NVARCHAR(MAX) ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources