Is there a way to prevent SQL Server silently truncating data in local variables and stored procedure parameters?
- by Luke Woodward
I recently encountered an issue while porting an app to SQL Server. It turned out that this issue was caused by a stored procedure parameter being declared too short for the data being passed to it: the parameter was declared as VARCHAR(100) but in one case was being passed more than 100 characters of data. What surprised me was that SQL Server didn't report any errors or warnings -- it just silently truncated the data to 100 characters.
The following SQLCMD session demonstrates this:
1 create procedure WhereHasMyDataGone (@data varchar(5)) as
2 begin
3 print 'Your data is ''' + @data + '''.';
4 end;
5 go
1 exec WhereHasMyDataGone '123456789';
2 go
Your data is '12345'.
Local variables also exhibit the same behaviour:
1 declare @s varchar(5) = '123456789';
2 print @s;
3 go
12345
Is there an option I can enable to have SQL Server report errors (or at least warnings) in such situations? Or should I just declare all local variables and stored procedure parameters as VARCHAR(MAX) or NVARCHAR(MAX)?
