Hi there! Been a while. Like "seven months a while" since my last post. But that's fine, because I've been working on this super complex query below:
But then I ran into a bug at my daily work... and it was so weird... so very very weird... I had a process that would pick up a bunch of data and do stuff with it, but there was this one row that was never being picked up! I couldn't figure out why until I tried trouble shooting by eliminating all the other rows in my test database to make stuff as clean and easy to read as possible.
So I just straight out flushed all other rows from existence, and was just about to break the query up into parts to find out where it went wrong, when the damn row all of a sudden decided to be included into the query... what. da. hell.
declaring a local variable or table has some hidden aspects to it, and pairing that with the use of cursors may be confusing to people who are used to coding like C++ or the likes.
I generally don't like cursors, but if you really have to use them, know that variables in SQL Server is batch based, not scope based.
What do I mean by that? Well...
Here's the most simple example I could fathom. We got a table that pushes its values into a cursor. This cursor will, inside each loop, declare a local table variable and shove the data of this cursor loop into that table variable.
So the first loop, we will create a table variable and insert the value 1, then select the data from the table variable.
In the second loop, we will again create a table variable and insert the value 2, then select that value of only 2 from the table variable... right? RIGHT?
No.
Variables are created once and re-used throughout the entire cursor. Neat right?