Menu
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.
258
In conclusion, use: SET : When you want to assign a single value to a variable and your variable is for a single value. SELECT : When you want to assign multiple values to a variable. The variable may be a table, temp table or table variable etc.
We can usually use SET and SELECT alternatively without any effect. A "SET" expression sets the specified local variable created previously to the given value. Here @localvariable is a local variable with any data type or cursor and value is any expression valid in SQL Server.
Use the SET statement to assign a value that isn't NULL to a declared variable. The SET statement that assigns a value to the variable returns a single value. When you initialize multiple variables, use a separate SET statement for each local variable.
If the assigning subquery returns multiple values, using the SET statement to assign value to a variable will raise an error as it only accepts a single value, where the SELECT statement will assign the last returned value from the subquery to the variable, with no control from your side.
SELECT is ANSI, SET @LocalVar is MS T-SQL
SELECT allows multiple assignents: eg SELECT @foo = 1, @bar = 2
187
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
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
