SQL SERVER – FIX : ERROR Msg 244, Level 16, State 1 – FIX : ERROR Msg 245, Level 16, State 1

FIX : ERROR Msg 244, Level 16, State 1, Line 1
FIX : ERROR Msg 245, Level 16, State 1, Line 1
This error can happen due to conversion of one data type to incompatible datatype.

Few examples are:
VARCHAR to INT, INT to TINYINT etc.

I have spotted this error happening with CAST or ISNULL, please add comments if you have come across this error in other examples. Following scripts will create this error.
SELECT CAST('111111' AS SMALLINT);
SELECT CAST('This is not smallint' AS SMALLINT);
The errors received from above two scripts are :

Msg 244, Level 16, State 2, Line 1
The conversion of the varchar value ‘111111’ overflowed an INT2 column. Use a larger integer column.
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘This is not smallint’ to data type smallint.

Interesting detail about ISNULL(@var1, @var2) is that it always cast @var2 to @var1. ISNULL function can generate the same error demonstrated in following script.
DECLARE @MyVal TINYINT
SELECT ISNULL(@MyVal,'123456') AS MyVal
The error received from above script is :
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ‘LongString’ to data type tinyint.

Fix/Solution/WorkAround:
Use CAST to convert to correct data type – change the data or change the data type.

Examples using workaround:
SELECT CAST('111111' AS VARCHAR(6));
SELECT CAST('111' AS SMALLINT);
DECLARE @MyVal TINYINT
SELECT ISNULL(@MyVal,'123') AS MyVal

Reference : Pinal Dave (https://blog.sqlauthority.com)