I have effectively added to my confusion level when it comes to C# and SQL Server. My most recent frustration appeared when creating a loop in a stored procedure. In a nutshell, I was trying to order a funky sort of tree (First Child, Next Sibling type relationships).
I had a clean recursive query that worked like a champ until I ran into an issue with 32 nested levels. If you hit that like I did, you need to do a loop. Don't get me wrong, 32 is plenty deep. However, when I had a malformed tree (something got messed up importing data) I couldn't even pull the sections to my app since it would just error out.
Well, not big deal, just write a loop, right? If I had totally understood the issues with NULL and variables, this would have been a knockout. As it was, I had some serious issues with my while loop.
WHILE (@Id <> NULL)
That won't do it folks. You need:
WHILE (IsNull(@Id, 0) > 0)
Interesting how in SQL Server, you can assign NULL to a variable but can't compare it to null and in C# you can't assign NULL to a native type (string, int), but can compare it to null. Hopefully this will help someone else out there.
