October 23, 2002 at 2:41 am
Please can someone explain why the following produces erroneous results :-
SELECT @variable_name = FROM {some_table} WHERE {some_condition}
IF @variable_name = NULL
BEGIN
... Do some stuff
END
ELSE
BEGIN
... Do some other stuff
END
The above set of statements produces strange results when executed in a loop where the {some_condition} is sometimes satisfied and other times not.
To guarantee the operation I have been forced to put :-
SELECT @variabl_name = NULL
before the statements then it all starts working as expected.
This has bugged the hell out of me for some time now. I even played with putting ISNULL({some_data}, -1) etc.... and changing the test afterwards, but the results were still spurious.
Any words of wisdom would be greatly appreciated.
Confused Of England
October 23, 2002 at 3:43 am
The reason is that you need to cover the eventuality that your <some condition> is going to fail sometimes. If you don't reset the value or use ISNULL() to substitute a value so that you know the outcome then you will get these unpredictable results.
Regards
Simon, also in the UK, and still looking for a job
October 23, 2002 at 4:18 am
If no record is returned from your select statement then the value in your variable will not be changed. To identify this situation check @@rowcount to see if it is 0, if this is the case then your select statement did not return any rows.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 23, 2002 at 4:34 am
Yes Simon, I would add though for swjs benefit that the test on @@rowcount should be inserted immediately after the SELECT else the result cannot be guaranteed to be relevant.
Rgds, Simon C
October 23, 2002 at 4:45 am
Consider what happens if no record meets the some_condition. In that case there are no rows returned which in turn means there isn't even a NULL value to work with. When @variable_name is created it is done so with an unknown value, this means unknown until affected to change and may not reflect as NULL for some reason. If your select returns no rows then it will not affect the variable. If you want a specific value to check for then SET the value of the variable before you run the SELECT (SELECT @variable = NULL works but this has to go thru the Query Manager to complete, it is better to use SET @variable = NULL since it doesn't make this trip). Now the reason you have to SET = NULL to check for @variable_name is that you want to know if the internal value = NULL and thus unknown does not. However, if you instead perform @variable_name IS NULL then it will read the value and comback as unknown thus return TRUE on the IS NULL, this is sort of misleading since it = NULL would seem to be the same, but in memory variables are not allocated physical memory address until set so the concept of NULL does not exist, when you SET = NULL it sets the memory address and the data within to NULL. IS NULL looks at if the memory has been assigned and the value within the memory (funny thing is that setting value = NULL takes more memory memory than not setting since the variable declaration takes up a bit of memory and the data takes up another bit of memory).
For you case use IS NULL and don't set.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 10/23/2002 04:46:38 AM
October 23, 2002 at 5:15 am
Well I think that about sums it up, lol - hope you're paying attention at the back - there will be questions at the end...
October 23, 2002 at 6:28 am
Can we put a limit on paragraph size. My mind switches off after 4 lines (2 after friday lunchtime)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 23, 2002 at 7:31 am
Sorry, just want to cover the why. Technically, however, I never hit enter so the paragraph is actually 1 line long.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 24, 2002 at 1:39 am
Lads,
You all done good. Thanks for de-mystifying the issue for me.
I'm not too keen on the @@rowcount check because it does not look logical. eg..
SELECT @variable_name = {some_data} FROM {some_table} WHERE {some_condition}
IF @@ROWCOUNT
BEGIN
Do Something ....
END
ELSE
BEGIN
Do Something Else...
END
I feel I need to test the variable that was part of the SELECT to make me feel better. I dont feel like there is a logical connection. Call me old fashioned! But hey the @@ROWCOUNT method is another Cat-Skinner - Thanks.
I have had odd results using the ISNULL() within the SELECT statement. So am still a little confused about how that could work.
But if you all agree that initialising to NULL prior to execution will work, then that's alright by me.
Again, thanks for all your postings.
Less Confused of England
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply