Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: Set @local VS Select @local

    In the article "SELECT @local_variable" SQL Server BOL recommends that SET be used to initialize local variables and that multiple SET statements should be used to populate multiple variables instead of using one SELECT with a list of variables. There is a pointer to see "SET @local_variable" for more information. While there are differences in the behavior of each method neither article explains the reasoning behind this recommendation to use SET vs SELECT. I would appreciate it if anyone could provide some additional info on this.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Set @local VS Select @local

    Sounds difficult and complicated, but it isn't.


    SET @Local_Variable assigns a value to your @Local_Variable; the first time it's an initialization, too.

    SELECT displays the value of @Local_Variable
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is more complicated.

    EFord is talking about the use of SELECT to assign values. Here is a code sample:

    declare @Local int
    set @Local = 1
    select @Local
    select @Local = 2
    select @Local

    Result set:
    -----------
    1

    -----------
    2

    I wasn't able to find anything as to why SET is better than SELECT, but I would hazard a guess that using SELECT might invoke unnecesarrily invoke the optimizer, while SET would not. Personally, I think distinguishing SET from SELECT just makes the code more readable.

    blindman

  4. #4
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I curious about the answer too.

    I think what EFord ment was during initialization we can do it 2 ways:


    set @test1=(select test1 from table1)
    set @test2=(select test2 from table1)

    select @test1,@test2

    or

    select @test1=test1,@test2=test2 from table1

    select @test1,@test2

    Posted the message about the same time as blindman, so we had almost identical explantions, but I like to ask 1 more question

    Why would 'set' be better if I can just use a 1 line select statement to initialize my @ variables. Saves me the hassle to code too many "sets".

    Last edited by Patrick Chua; 08-18-03 at 14:49.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There appears to be no difference in utilization of server resources between SET and SELECT as far as assigning a value to a local variable.

    Both statements yield 2 memory reads and optimizer is not participating in SELECT.

    But I would agree with blindman that the code looks "more readable."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •