What is the difference between SET and SELECT when assigning values to variables, in T-SQL?

I will highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.
DECLARE
SELECT @Variable1 AS int, @Variable2 AS int @Variable1 = 1 SET @Variable2 = 2 SET is the ANSI standard way of assigning values to variables, and SELECT is not

U can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time.
Here's how:
DECLARE @Variable1 AS int, @Variable2 AS int @Variable1 = 1, @Variable2 = 2SET @Variable1 = 1SET

So far so good
DECLARE
SELECT. But if you ever wrote error handling code in T-SQL, you most probably are aware that, the system variables @@ERROR and @@ROWCOUNT must be captured in one statement, immediately after a data manipulation (DML) statement like INSERT, UPDATE, DELETE, or else, these system variables get reset to 0. So, if you want to stick to the standards and use SET in this scenario, you are out of luck. The following example demonstrates the problem: @Error int, @RowCount int price/0 FROM dbo.titlesSET @RowCount = @@ROWCOUNTSET @Error = @@ERRORSELECT @Error AS ErrorGO
If
DECLARE
SELECT you run the above piece of code in pubs database, the value of @@ERROR system variable will be displayed as 0, even though the 'division by zero' resulted in error 8134. So, in this particular scenario, forget about standards and use SELECT, as shown below: @Error int, @RowCount int price/0 FROM dbo.titlesSELECT @RowCount = @@ROWCOUNT, @Error = @@ERRORSELECT
But
DECLARE
SELECT @Error AS Errorif you insist on using SET even in this scenario, there's always a way out. Here's one example, though not readable and recommended: @ErrorAndRowcount AS varchar(25), @Error int, @RowCount int price/0 FROM dbo.titlesSET @ErrorAndRowcount = CAST(@@ERROR AS varchar(12)) + '.' + CAST(@@ROWCOUNT AS varchar(12))SET @Error = CAST(PARSENAME(@ErrorAndRowcount, 2) AS int)SET @RowCount = CAST(PARSENAME(@ErrorAndRowcount, 1) AS int)SELECT @Error AS Error, @RowCount AS Row_CountSET @Error = CAST(LEFT(@ErrorAndRowcount, CHARINDEX('.', @ErrorAndRowcount)-1) AS int)SET @RowCount = CAST(RIGHT(@ErrorAndRowcount, CHARINDEX('.', REVERSE(@ErrorAndRowcount))-1) AS int)SELECT @Error AS Error, @RowCount AS Row_CountGOMoving
SET
CREATEon to other differences between SET and SELECT: When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT, and this type of bugs are hard to track down too. Here is an example: NOCOUNT ON TABLE #Test (i int, j varchar(10))INSERT INTO #Test (i, j) VALUES (1, 'First Row')INSERT INTO #Test (i, j) VALUES (1, 'Second Row')GO
DECLARE @j varchar(10)SELECT @j = j FROM #Test WHERE i = 1SELECT @jGO
DECLARE @j varchar(10)SET @j = (SELECT j FROM #Test WHERE i = 1)SELECT @jServer:
Subquery returned more than 1 value Msg 512, Level 16, State 1, Line -1074284106. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Based
DECLAREon the above results, when using a query to populate variables, I suggest you always use SET, if you want to be sure that only one row is returned. If you hate SET for some reason, you could get the same behavior of SET, using SELECT, as shown below: @j varchar(10)SELECT @j = (SELECT j FROM #Test WHERE i = 1)SELECT
Here
/* Returns NULL */
DECLARE @Title varchar(80)
SET @Title = '
SET @Title =
(
SELECT title
FROM dbo.titles
WHERE title_id = '
)
SELECT @Title
GO
/* Returns the string literal '
DECLARE @Title varchar(80)
SET @Title = '
SELECT @Title = title
FROM dbo.titles
WHERE title_id = '
SELECT @Title
GO @jis another difference with respect to assigning values based on a query, especially when the query doesn't return any rows. Run the following example in the pubs database, and you will see what I mean:Not Found'InvalitTitleID'Not Found' */Not Found'InvalitTitleID' @Variable2 = 2
SELECT