Declare Variable

  • Samith C

    Mr or Mrs. 500

    Points: 519

    Comments posted to this topic are about the item Declare Variable

    [font="Verdana"] There is no Wrong time to do a Right thing 🙂 [/font]

  • Hany Helmy

    SSChampion

    Points: 13488

    Nice & easy.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    The question itself is great, however the explanation is lacking.

    There's no reference, and the explanation itself is incorrect.

    There's no variable that is "reset".

    The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    And again, nice and easy wins the race.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    Koen Verbeeck (9/15/2013)


    The question itself is great, however the explanation is lacking.

    There's no reference, and the explanation itself is incorrect.

    There's no variable that is "reset".

    The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.

    +1

    Basic question.

    🙂

  • sqlnaive

    SSCoach

    Points: 17435

    Koen Verbeeck (9/15/2013)


    The question itself is great, however the explanation is lacking.

    There's no reference, and the explanation itself is incorrect.

    There's no variable that is "reset".

    The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.

    Definitley +1. Thanks for giving the extra information Koen. 🙂

    In between, good question.

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    In the following batch @j is declared once, but initialized at every loop:

    declare @i int

    set @i = 2

    while @i < 10

    begin

    declare @j int = @i

    print @i

    print @j

    set @i += 1

    end

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • twin.devil

    SSC-Insane

    Points: 22208

    nice basic question

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    Stewart "Arturius" Campbell (9/16/2013)


    Carlo Romagnano (9/16/2013)


    In the following batch @j is declared once, but initialized at every loop:

    declare @i int

    set @i = 2

    while @i < 10

    begin

    declare @j int = @i

    print @i

    print @j

    set @i += 1

    end

    remember, within the confines of a while loop, each iteration is a new scope.

    No, the scope is to the end of the batch, here, you can reference @j out of the loop.

    declare @i int

    set @i = 2

    while @i < 10

    begin

    declare @j int = @i

    print @i

    print @j

    set @i += 1

    end

    print @j

    [/quote]

  • psingla

    Hall of Fame

    Points: 3840

    Koen Verbeeck (9/15/2013)


    The question itself is great, however the explanation is lacking.

    There's no reference, and the explanation itself is incorrect.

    There's no variable that is "reset".

    The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.

    Hopefully that's why we can not declare same variable multiple times in different scopes.Like we can do in c,C++,Java,SSIS etc

    As per me it's not properly handled by the parser.

    Either variable declaration within a "if block" should not be allowed otherwise variable scope should be limited to "if block" only.

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Koen Verbeeck (9/15/2013)


    The question itself is great, however the explanation is lacking.

    There's no reference, and the explanation itself is incorrect.

    There's no variable that is "reset".

    The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.

    +1, the declare statement is not ignored as is parsed before the execution starts. If not,

    print @j

    would always fail, as wouldn't be declared.

    Good question, though

  • Dscheypie

    SSCommitted

    Points: 1545

    Carlo Romagnano (9/16/2013)


    No, the scope is to the end of the batch, here, you can reference @j out of the loop.

    declare @i int

    set @i = 2

    while @i < 10

    begin

    declare @j int = @i

    print @i

    print @j

    set @i += 1

    end

    print @j

    [/quote]

    Good point, Carlo, but the loop is interpreted later!

    Look at this:

    declare @i int

    set @i = 2

    set @j = 1

    while @i < 10

    begin

    declare @j int = @i

    print @i

    print @j

    set @i += 1

    end

    print @j

    This returns an error message:

    Must declare the scalar variable "@j".

    This means in my opinion:

    The script is interpreted before the execution and variables get declared then, EXCEPT for constructs like the loops which are in there own scope and therefore interpreted later.

    Best regards

    Jens-Peter 🙂

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    Dscheypie (9/16/2013)


    Carlo Romagnano (9/16/2013)


    No, the scope is to the end of the batch, here, you can reference @j out of the loop.

    declare @i int

    set @i = 2

    while @i < 10

    begin

    declare @j int = @i

    print @i

    print @j

    set @i += 1

    end

    print @j

    Good point, Carlo, but the loop is interpreted later!

    Look at this:

    declare @i int

    set @i = 2

    set @j = 1

    while @i < 10

    begin

    declare @j int = @i

    print @i

    print @j

    set @i += 1

    end

    print @j

    This returns an error message:

    Must declare the scalar variable "@j".

    This means in my opinion:

    The script is interpreted before the execution and variables get declared then, EXCEPT for constructs like the loops which are in there own scope and therefore interpreted later.

    Best regards

    Jens-Peter 🙂

    The scope is TO THE END of the batch and not before the declaration.

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply