I was playing in SQL Server this morning, trying to fix an odd bug. Took me a while to find it and I thought I’d share this tidbit with you.

Here’s an overly simplistic representation of what was causing the “String or binary data would be truncated.” error message:

   1:  declare @where nvarchar(max);
   2:  -- assume something put a large string in @where (over 8000 characters). 
   3:  declare @sql nvarchar(max);
   4:  select @sql = replace(‘select * from table1 where {0} order by column1 asc’, ‘{0}’, @where);
   5:  exec (@sql);

 

The reason I experienced this error is because of how replace handles nvarchar(max). By explicitly casting the first parameter to nvarchar(max), the error is resolved.

   1:  declare @where nvarchar(max);
   2:  -- assume something put a large string in @where (over 8000 characters). 
   3:  declare @sql nvarchar(max);
   4:  select @sql = replace(cast(‘select * from table1 where {0} order by column1 asc’ as nvarchar(max)), ‘{0}’, @where);
   5:  exec (@sql);

 

From the documentation:

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.