Post reply

What is the difference between SET and SELECT?

  • July 26, 2002 at 2:19 pm

    #79156

    Is there any difference between SET and SELECT when assigning a value to a variable? They both produce the same result. Is one preferred over the other?

    DECLARE @comments char(6)

    set @comments = 'Hello!'

    PRINT @comments

    select @comments = 'Hello!'

    PRINT @comments

    Thanks in advance

    Billy

  • July 29, 2002 at 4:29 pm

    #433599

    SET is preferred over SELECT for simple variable assignments. (BOL - look under variable, assigning)

    SELECT is typically used to assign the results of a T-SQL query into the specified variable.



    -Brandon

  • July 29, 2002 at 5:42 pm

    #433601

    I don't think there is a performance difference, but I could be wrong.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • July 29, 2002 at 7:43 pm

    #433609

    Thanks for the BOL reference. oh great. They recommend you that "...SET @local_variable be used for variable assignment rather than SELECT @local_variable..." but they don't really tell you why...

    not sure about the performance issue either..

    I am asking because I see both SELECT and SET used for variable assignments in system SPs... although when either one of them is used, it is used in uniform throughout the SP.

  • August 1, 2002 at 8:30 am

    #433806

    SET is preferable, because it is ANSI complient (so was I told by an experienced MVP, I didn't look up the ANSI spec myself).

    With SELECT you can assign values to multiple variable at a time (SELECT @i = 'a', @j-2 = 'b'). You can't do this with SET. So, you must use SELECT to capture the values of both @@ERROR and @@ROWCOUNT at the same time.

    Another reason why I use SET is that, SET will fail if more than one value is returned by the query, where as SELECT will simply assign the last value from the returned list, there by making any bugs in the query. For example:

    SET @Email = (SELECT Email FROM Customer WHERE cust_id = 10)

    The above statement will fail if you have more than one row for the cust_id 10. But if you rewrite the above with SELECT, it will simply assign one of those values and keep quiet.

    When it comes to performance, I haven't seen any differences.

    HTH,

    Vyas

    http://vyaskn.tripod.com/


    HTH,
    Vyas
    SQL Server MVP
    http://vyaskn.tripod.com/

Viewing 5 posts - 1 through 4 (of 4 total)

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