Initialising T-SQL Variables before using a SELECT

  • 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

  • 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

  • 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

  • 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

  • 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

  • Well I think that about sums it up, lol - hope you're paying attention at the back - there will be questions at the end...

  • 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

  • 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)

  • 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