Aaron N. Cutshall (8/10/2009)
I've been burnt by this "feature" too often to not recognize it! It took me hours to track down an issue cause by a string truncation that was being passed as a parameter to an SP. Increasing the parameter size was all it took to fix it.Time spent tracking down the problem: 6.5 hours
Time spent actually fixing the problem: 5 seconds
It can get even more interesting (and fun to debug) if the value of the parameter can be evaluated by the engine to one of the numeric data types, such as int. Then you can get a * as a result of the procedure execution. For example,
create proc dbo.testProc
@testVar varchar(4)
as
select @testVar
GO
exec testProc 123456
GO
The result of the above is (with results to text option)
----
*
(1 row(s) affected)
While this behavior is by design, it still is a very nice curve ball 🙂
Oleg