views:

151

answers:

5

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.

+6  A: 

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

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

gbn
Your first point is contradicted by many sources in @breitak67's answer (http://stackoverflow.com/questions/1034634/what-is-the-difference-between-select-and-set-in-t-sql/1034756#1034756)
Michael Haren
Damn! I'm getting old. However, the SELECT is also in ANSI now, I think.
gbn
Interesting that Sybase does not mention SET @varhttp://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug598.htm
gbn
+2  A: 

Select allows multiple assignments.

EDIT you beat me by 44 seconds

Eppz
+3  A: 

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

breitak67
A: 

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

AlexKuznetsov
Given you still have to write the values into memory, I doubt it. It's still 2 assignment whether it's 2xSET or 1xSELECT.
gbn
gbn, I provided benchmarks. you can run benchmarks and see for yourself
AlexKuznetsov
A: 

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

HLGEM