Stored Proc/Table Truncation

Stored Proc/Table Truncation

• SRobb

  SET ANSI_WARNINGS will work for most cases. But that will still not catch cases like passing values to stored procedure parameters. In this case, the parameter value will be silently truncated and your insert/update that uses the variables will not see the bad input in the first place. There is also the issue of implicit conversion in TSQL in expressions, calculations etc that can hide overflow or rounding errors for example. So it is hard to enforce this in TSQL. You should always validate the data in the client side.
  
  

• Gwenael

  SEE ANSI_WARNINGS
  
  Please try the following; it works for me in SQL8.0/7.0
  
  
  SET ANSI_WARNINGS ON

  create TABLE #test ( t1 varchar(3) not null);

  INSERT #test (t1) values ('test')

  
  SELECT * FROM #test

  DROP TABLE #test
  
  gives
  
  Server: Msg 8152, Level 16, State 9, Line 11
  String or binary data would be truncated.
  The statement has been terminated.
  t1
  
  Tim S

  
  
• Stored Proc/Table Truncation