Hugo Kornelis (5/1/2013)
I think Ron expected the ANSI_WARNINGS setting to cause errors. Either because of the implicit conversion of 12.5 to integer, or because of the string truncation.
However ...
1. Rounding numerical data is not an overflow condition; AFAIK this does not raise an error regardless of ANSI_WARNINGS setting;
2. String truncation does cause an error to occur with ANSI_WARNINGS on - but only if it occurs while inserting or updating, not when assigning to a variable. This is mentioned explicitly in the Books Online article (http://msdn.microsoft.com/en-us/library/ms190368.aspx).
If you increase the length of @s-2 to 11 characters or more, the effect of ANSI_WARNINGS does kick in. An error is returned during the INSERT statement, and the final SELECT returns nothing at all. (Since the INSERT errored, no row was inserted, so there is nothing to return).
Took me a while to understand, what you wrote there, Hugo, but:
"[...] string too large for database column [...]" in the article http://msdn.microsoft.com/en-us/library/ms190368.aspx made it clear after all. Thanx for your explanation, Hugo, and also thanx to the author of the QotD!
________________________________________________________
If you set out to do something, something else must be done first.