Differences between Select and SET on variable assignments in SQL Server stored procedures

Source: Internet
Author: User
Tags sql server books scalar

Transferred from: http://www.cnblogs.com/micheng11/archive/2008/07/08/1237905.html

There are two ways to assign values to variables that are already defined in SQL Server, SET and SELECT, respectively.
The differences between these two approaches are explained in more detail in SQL Server Books Online, but many times we
Did not notice, in fact, these two ways still have a lot of differences.

SQL Server recommends using SET instead of SELECT to assign a value to a variable.
The SET method is recommended when an expression returns a value and assigns a variable to it.

The following table lists the differences between SET and SELECT. Please pay special attention to the red section.

Set Select
Simultaneously assign values to multiple variables simultaneously Not supported Support
When an expression returns multiple values Error Assigns the last value returned to the variable
Expression does not return a value variable is assigned a null value The variable holds the original value

Here's a specific example to illustrate the problem:

CREATE TABLE Chinadba1 (
UserID int,
Addr varchar (128)
)
Go
Insert into CHINADBA1 (USERID,ADDR) VALUES (1, ' ADDR1 ')
Insert into CHINADBA1 (USERID,ADDR) VALUES (2, ' ADDR2 ')
Insert into CHINADBA1 (USERID,ADDR) VALUES (3, ' ADDR3 ')
Go

When an expression returns more than one value, use SET to assign a value


DECLARE @addr varchar (128)
Set @addr = (select addr from Chinadba1)
/*
--The error message is
Server: Msg 512, Level 16, State 1, line 2
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.
*/
Go

When an expression returns multiple values, use SELECT to assign a value

DECLARE @addr varchar (128)
Select @addr = addr from Chinadba1
Print @addr-The value of the last addr column in the result set
--Result: ADDR3
Go

When an expression does not return a value, use SET to assign a value

DECLARE @addr varchar (128)
Set @addr = ' initial value '
Set @addr = (select addr from chinadba1 where UserID = 4)
Print @addr--null value
Go

Use SELECT to assign a value when an expression does not return a value

DECLARE @addr varchar (128)
Set @addr = ' initial value '
Select @addr = addr from chinadba1 where UserID = 4
Print @addr--Keep the original value
Go

It is important to note that SELECT can also assign the value of the scalar subquery to a variable, and if the scalar subquery does not return a value, the variable is set to a null value.
This is exactly the same as using SET assignment
The concept of scalar quantum query everyone should be unfamiliar, for example, can explain

DECLARE @addr varchar (128)
Set @addr = ' initial value '
--select addr from chinadba1 where UserID = 4 for scalar quantum query statements
Select @addr = (select addr from chinadba1 where UserID = 4)
Print @addr--null value
Go

Differences between Select and SET on variable assignments in SQL Server stored procedures

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.