Set assigns a null value to the variables, when you assign from the query and the query returns no result. Example: DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SET @CustomerID = (SELECT [CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = 'ABC') SELECT @CustomerID -– Returns NULL
| When you assign from a query and the query returns no result. SELECT doesn’t make the assignment and hence it can’t change the value of the variable Example: DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = 'ABC' SELECT @CustomerID –- Returns XYZ
|
When you assign from the query that returns more than one value, then SET results into an error Example: SET = (SELECT [CustomerID] FROM [dbo].[Customers])
Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. | When you assign from the query that returns more than one value, SELECT assigns the last value returned by the query and hides the fact that the query returned more than one row. Example: SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers]
-- No error generated |