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.
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) ?? |
 |
|
|
|
|