Hugo Kornelis (2/1/2010)
SELECT HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 8000)) returns a 20-byte value, and SELECT HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000)) returns the same warning message and no value. Why the IF comparison between the two doesn't abort but chooses to execute the ELSE clause is, in all honesty, beyond me. I firsth thought it was because the too-long input causes the function to return NULL (in addition to the message), but when I use IF HASHBYTES(...) IS NULL, I still got the "no" result.
This piqued my curiosity. Could the "no" result from your direct test
IF HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000))
is NULL
PRINT 'yes.';
ELSE
PRINT 'no.';
...be because the result of the IF test is NULL? When the HASHBYTES() function fails with the input truncation error, it does not return NULL, but rather doesn't return anything. Then, the IF statement executes and doesn't find a NULL, so returns FALSE, which of course leads to the "Print 'No'" branch.
Running in SSMS with results to text makes it a bit easier to see because it says "NULL" for null results and nothing for the failed HASHBYTES().
select IsNull(HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000)),0x2222aaaa9999)
select HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 4000))
Declare @MyNull varbinary
select @myNull
Returns
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0xD5FE2200837AB7B4FB073787F8E531D6C6A8CDD2
(1 row(s) affected)
----
NULL
(1 row(s) affected)