SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Varchar "miracle" and best practice

I've just spent fifteen minutes helping colleague to find "miracle in t-sql code".
- There should be rows, definitely! I can run simple query and voila! 4 thousands of rows selected! So why there are no rows when I'm doing a simple left join with small filter expression?
There is no miracle of course.

Just varchar variable. Without explicitly specified length!
As stated in MSDN - "When n is not specified in a data definition or variable declaration statement, the default length is 1." Such variable simply cannot contain five chars with filtering value - and query returns no rows.
Simply, isn't? And now try to find such error. It can be very fun.

So what can we conclude?
ALWAYS specify length for varchar fields and variables. If you really need one char variable - don't be shy, use varchar(1).

Btw, take a look at BP007. It can save you tons of time.


Leave a comment on the original post [sqlcodeguard.blogspot.com, opens in a new window]

Loading comments...