Is there a way to prevent SQL Server silently truncating data in local variables and stored procedure parameters?

Question

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

Answer 1

SQL Server has no such option. You will either have to manually check the length of strings in your stored procedure and somehow handle the longer strings or use the nvarchar(max) option. If disk space isn't an issue then the nvarchar(max) option is certainly the easiest and quickest solution.