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.
- 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.
Definitely a good catch. I've been running SQL Enlight as a test, and the free SQL Code Guard and finding that even though I know it's a bad practice I still forget to type precision levels in at times. Highly recommend some type of tool to double check your work like SQL Guard or Enlight.
ReplyDeleteAlso not that concatenation can get hairy if you start concatenating short strings without length declaration to longer varchar(max). I think it can implicitly take a shorter length if you don't "remind" SQL server. Cheer
I also had this issue and only googling helped me. Thanks for recap:)
ReplyDelete