When to assign a value to a variable using set and select

Original: When to assign a value to a variable using set and select

We often use set and select to copy variables, but sometimes we can only choose one to use, here we take a look at these examples, this example uses the AdventureWorks database to do the demonstration.

Return a value by query:

When you pay a variable the value returned by the query, set will accept the result (single value) and pay a scalar value. However, select can accept multiple values returned by the query.

Here's an example of using set for both single and multi-valued values:

Use adventureworksgo--returns a row using setdeclare @Var1ForSet varchar SET @Var1ForSet = (SELECT [Name] from Production.Product WHERE productnumber = ' hy-1023-70 ') PRINT @Var1ForSetGOUSE adventureworksgo--returns multiple rows using Setdeclare @Var2ForSet varchar (50) SET @Var2ForSet = (SELECT [Name] from production.product WHERE Color = ' Silver ') PRINT @Var2ForSetGO

You can see the following results:

When set is used as a multi-value assignment, an error is given. Because set rejects a fuzzy value (set does not know which value should be used to assign a value).

Let's take a look at the example of assigning a value with select:

Use adventureworksgo--a single value assignment by using SELECT declare @Var1ForSelect varchar SET @Var1ForSelect = (select [Name] from Productio N.product WHERE productnumber = ' hy-1023-70 ') PRINT @Var1ForSelectGO--  Multi-value assignment via select declare @Var2ForSelect varchar (50) SELECT @Var2ForSelect = [Name] from production.product WHERE Color = ' Silver ' PRINT @Var2ForSelectGO

You can see that both executions succeed.

The examples shown above can be used to check for problems. Some errors are tracked by set.

Assign multiple values to multiple variables:

When multiple variable values need to be assigned, it is preferred to use Select to get data from a query. Because you can assign values directly, instead of writing set every time. Take a look at the following example:

Use adventureworksgo--Direct Assignment DECLARE @var1 varchar (DECLARE) @var2 varchar (DECLARE @var3 varchar) SELECT @var1 = ' Val Ue1 ', @var2 = ' Value2 ', @var3 = ' Value3 ' PRINT @var1PRINT @var2PRINT @var3GO--Assigns a value DECLARE @name VARCHAR (DECLARE) by way of query @pr Oductno varchar (DECLARE) @color varchar () SELECT @name = [name], @productNo = productnumber, @color = Colorfrom Product Ion. Product WHERE ProductID = 320PRINT @namePRINT @productNoPRINT @colorGO

Get the following results:

If you need to use set, you must use several separate statements to implement:

Use adventureworksgo--Direct assignment DECLARE @var1 varchar DECLARE @var2 varchar () DECLARE @var3 varchar (SET @var1 = ' Value1 ' Set @var2 = ' Value2 ' Set @var3 = ' Value3 ' PRINT @var1PRINT @var2PRINT @var3 go--is assigned by query DECLARE @name VARCHAR DECLARE @p Roductno varchar (DECLARE) @color varchar (Set @name = (SELECT [name] from production.product WHERE ProductID =.) Set @productNo = (select ProductNumber from production.product WHERE ProductID =.) SET @color = (select color from Productio N.product WHERE ProductID =) PRINT @namePRINT @productNoPRINT @colorGO

Get results:

By contrast, it is obvious that select is much simpler and more efficient in the case of multi-value assignment because it is implemented at once.

What happens when the assignment fails?

When the assignment fails, the behavior of the set and select is not the same. The assignment failure here may be that there is no data or the data type is not worthy. The select returns the previous value (if the previous value has been successfully assigned), and set assigns NULL to the variable. In any case, this failed assignment will produce unpredictable results, so it needs to be handled carefully.

Take a look at the example below:

Use Adventureworksgo--shows the scenario where the set assignment failed declare @var1 VARCHAR set @var1 = ' Value 1 Assigned ' PRINT @var1SET @var1 = (SELECT Color from production.product WHERE ProductID = 32022) PRINT @var1GO--Shows the scenario where the SELECT assignment failed declare @var1 VARCHAR SELECT @var 1 = ' Value 1 Assigned ' PRINT @var1 SELECT @var1 = Color from production.product WHERE ProductID = 32023PRINT @var1GO

The results are as follows:

As you can see from the results, NULL is returned when the set fails, and the previous result is returned when select fails.

Standard:

Many times, using Select is a good choice, but assigning a value with select does not conform to the ANSI standard, so if you want to follow the standard (possibly because you are porting code to another DBMS), you should use set instead of select.

Summarize:

In fact, the best way is to prove it through practice. Many times it can be mixed, but sometimes it is recommended to use it alone:

Scenarios using Set:

• Direct Assignment is required and no queries are required, such as variable initialization.
• intentionally assigning a null value.
• Follow the ANSI standard for future porting.
• give a non-scalar value.

Use the Select scenario:

• directly assigns a multivalued variable.
• A variable is assigned by a query (either single-valued or multi-valued).
• Check the amount of code.
• In order to get the values of variables such as @ @ROWCOUNT and @ @ERROR.

When to assign a value to a variable using set and select