The difference between select and set in SQL-reprint

The following table lists the differences between SET and SELECT

	SELECT	SET
When multiple variables are simultaneously assigned to a value	Support	Not supported
When an expression returns multiple values	Assigns the last value returned to the variable	Error
When an expression does not return a value	The variable holds the original value	variable is assigned a null value

(1). When multiple variables are simultaneously assigned to a value

DECLARE @a varchar, @b varchar SET @a= ' ABC ', @b= ' EFG ' GO--Error: Msg 102, Level 15, State 1, line 3rd ', ' near syntax error.

DECLARE @a varchar (+), @b varchar SELECT @a= ' ABC ', @b= ' EFG ' GO--run correctly

(2). When an expression returns multiple values

Before we explain this, let's create a table to use and assign a value to it, with the following code:

Assignment Test Table--fuzhitest

int , name varchar (+)) Goinsert into Fuzhitest (id,name) VALUES (1,'name1') insert INTO Fuzhitest (Id,name) VALUES (2,'name2') insert into fuzhitest (id,name) VALUES (3,' name3') go                 

DECLARE @name varchar set @name = (select name from Fuzhitest) GO--Error: MSG 512, Level 16, State 1, line 2nd

The subquery returns more than one value. This is not allowed when subqueries are followed by =,! =, <, <=, >, >=, or when the subquery is used as an expression.

DECLARE @name varchar select @name =name from fuzhitest print @name GO--run correctly: Show Name3

(3). When an expression does not return a value

DECLARE @name varchar Set @name = ' Set initial value ' set @name = (select name from fuzhitest where id = 4) print @name GO --run correctly: Show null

DECLARE @name varchar Set @name = ' Select initial value ' select @name = name from fuzhitest where id = 4 print @name-- Run correctly: Displays the Select initial value

note: Select You can also assign the value of a scalar subquery to a variable, and if the scalar subquery does not return a value, the variable is set to a null value (

declare @name varchar (+) Set @name = ' Select initial value '

SOURCE http://www.cnblogs.com/4mylife/archive/2012/10/25/2738466.html

The difference between select and set in SQL-reprint