SQL Server Performance

Understanding the scope of variables

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Nov 11, 2009.

  1. shankbond New Member

    Hi,
    see this strange piece of code written :

    DECLARE @skill VARCHAR(1000)--takes the skills in the form of skill,NUM;skill,NUM
    --where skill is the varchar value and num will be the experience in years only
    DECLARE @candidate_id VARCHAR(10)--takes candidate id
    SET @candidate_id='cd54'
    SET @skill='asp.net,15;sql dba,30;vb.net,1.23'
    DECLARE @getskill VARCHAR(50)
    DECLARE @getexperience DEC(6,4)
    WHILE(CHARINDEX(';',@skill)<>0)
    BEGIN --begin while

    SET @getskill=LEFT(@skill,CHARINDEX(',',@skill)-1)
    SET @skill=RIGHT(@skill,LEN(@skill)-CHARINDEX(',',@skill))
    SET @getexperience=CONVERT(DEC(6,4),LEFT(@skill,CHARINDEX(';',@skill)-1))
    SET @skill=RIGHT(@skill,LEN(@skill)-CHARINDEX(';',@skill))

    PRINT @getskill
    PRINT @skill
    PRINT @getexperience


    END --end while
    SELECT @getskill,@skill,@getexperience


    --SET @getskill=LEFT(@skill,CHARINDEX(',',@skill)-1)
    --SET @skill=RIGHT(@skill,LEN(@skill)-CHARINDEX(',',@skill))
    --SET @getexperience=@skill
    --SELECT @getskill,@skill,@getexperience

    I want to point out the bold characters here, I don't understand how are the values of variables gettting reinitialised?
    what is the scope of the local variables?
    I hope You are understanding, what I am trying to explain.
    Any help shall be appreciated!
  2. Adriaan New Member

    The variables are initialized by the first SET command, and they would be re-set by the SET command in the WHILE loop.
    Note that unlike many other programming environments, T-SQL does not apply a default value to variables - variables always start off as NULL.
    For local variables in a script, the scope is between the declaration of the variable and the end of the current batch (like a line that says just GO).
  3. shankbond New Member

    Hi Adriaan,
    [quote user="Adriaan"]For local variables in a script, the scope is between the declaration of the variable and the end of the current batch (like a line that says just GO).[/quote].
    As You can see the script that I have not used any GO command.
    I am actually unable to understand ,If You run the above script I have used two types of Select statements one which run inside the while loop and others which run outside the while loop.
    In this script I am basically trimming the @skill by a semicolon and putting the values in the respective variables, the while loop will run the entire @skill until no semicolon is encountered that is it will be the last skill set of a candidate, now when I run the (select)statements outside the while loop to test they are simply showing me the original values that were initialized in the very beginning of the script as if no manipulation have been done on it?
    Please help
  4. Madhivanan Moderator

    Note that you only assigned values to the variables @candidate_id and @skill
  5. shankbond New Member

    Hi,
    [quote user="Madhivanan"]Note that you only assigned values to the variables @candidate_id and @skill[/quote]
    Yes ,but I only initialized them once in the beginning, and I was constantly changing the values during every iteration of the loop, but after the loop ends, I get the very first values of the variables?
  6. Adriaan New Member

    SELECT @myvariable does not assign a value to the variable, it will only make the current value appear on the Results tab.
    To assign a value, you can use SET @myvariable = 'a' or SELECT @myvariable = 'a'.
    If you want to add a new item to the string in an iteration, you use
    SET @myvariable = @myvariable + @separator + @newvalue
    or
    SELECT @myvariable = @myvariable + @separator + @newvalue
  7. shankbond New Member

    Hi Adriaan and others,
    [quote user="Adriaan"]
    SELECT @myvariable does not assign a value to the variable, it will only make the current value appear on the Results tab.
    To assign a value, you can use SET @myvariable = 'a' or SELECT @myvariable = 'a'.
    [/quote]
    I know what a select and a set statement does, but this is a strange behaviour; Yesterday when I posted this thread the two sets of print and select statements showed different results for @skill so I was a bit confused over the scope of local variables and started the thread but today when I run this same query now both of the sets are showing the same results (which was desired).
    How can that be possible that a same instruction return different results at different times? [:|]

Share This Page