What is the difference between SELECT and SET in T

问题:

I'm working on a stored proc that executes some dynamic sql. Here's the example I found on 4GuysFromRolla.com

CREATE PROCEDURE MyProc
  (@TableName varchar(255),
   @FirstName varchar(50),
   @LastName varchar(50))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)

    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM " +
                   @TableName + "WHERE FirstName = '"
                   + @FirstName + "' AND LastName = '"
                   + @LastName + "'"

    -- Execute the SQL statement
    EXEC(@SQLStatement)

If you notice, they are using the keyword SELECT intead of SET. I didn't know you could do this. Can someone explain to me the differences between the 2? I always thought SELECT was simply for selecting records.

回答1:

SELECT is ANSI, SET @LocalVar is MS T-SQL

SELECT allows multiple assignents: eg SELECT @foo = 1, @bar = 2

回答2:

Basically, SET is SQL ANSI standard for settings variables, SELECT is not. SET works only for single assignments, SELECT can do multiple assignments. Rather than write a long explanation that is well summarized in many places on the net:

ryan farley blog

tony rogerson

stackoverflow

回答3:

Select allows multiple assignments.

EDIT you beat me by 44 seconds

回答4:

SELECTs may be faster if you need to assign multiple values:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/25/defensive-database-programming-set-vs-select.aspx

回答5:

Select can also be used to get the variable assignment from a select statement (assuming the statement only returns one record)

Select @myvariable = myfield from my table where id = 1