by Ross Mack - GUI Computing
'After Scooby fell out of the laundry shoot straight onto ADO we knew we had it pinned. Fred stepped forward and boldly tore of the scary mask it was wearing. Underneath it was old Mr. ODBC from the amusement park. "I would have got away with it, too. If it weren't for you pesky developers !" he said.
Then Shaggy appeared from behind the shifting bookcase with a hamburger and we all laughed.'
Recently I spent quite a while belting through an app I have been working on and turning a few functions and lookups and such into views or stored procedures to increase efficiency. What a grand plan !
I mean, after all it's the sort of thing experienced and competent developers do. At least, that's what I am told. It even seemed to be yielding some good performance.
However, after a couple days of this I went back to the application to see how everything was working out with the new calls. And what did I see ? A Frog ? No. I didn't see much data in many cases either. I went back and checked my views and stored procs. Everything seemed to be OK... Was I losing my Mind ? Again ?
Running the queries directly in enterprise manager, or VB's DataView (for which I could not have more adoration without seeming perverted) seemed to return data quite smartly. Everything seemed to be going fine. Then I noticed the sorts of places where it was going wrong. Mostly the problems occurred with concatenated fields. Consider a view like this:
CREATE VIEW vwPeople AS SELECT FirstName + ' ' + LastName AS Display, PersonID FROM tblPerson WHERE PersonDeleted = 0The problem was that nothing was being returned for many of the rows. After a little snooping I discovered that the row would be returned blank if either FirstName or LastName were NULL.
The trick was that this seemed to only occur from VB. I guess the way Enterprise manager connects to the server is a little different. Anyway the easiest workaround for this is to use the ISNULL SQL function. It simply allows you to replace a NULL value with something else. So, to rewrite the View above it would end up looking like this:
CREATE VIEW vwPeople AS SELECT ISNULL(FirstName,'') + ' ' + ISNULL(LastName,'') AS Display, PersonID FROM tblPerson WHERE PersonDeleted = 0An even better version would be:
CREATE VIEW vwPeople AS SELECT LTRIM(ISNULL(FirstName,'') + ' ' + ISNULL(LastName,'')) AS Display, PersonID FROM tblPerson WHERE PersonDeleted = 0Which uses a left trim function to avoid having a space as the first character, which would occur when the FirstName field is NULL.
I hope this quick hint helps save someone out there a few frustrating hours of head scratching.