In the stored procedure, select and set assign values to variables.

Source: Internet
Author: User

SQL Server assigns values to Defined variables in two ways:Set and select. The difference between the two methods has been described in detail in the SQL Server online series, but we do not pay attention to the fact that there are many differences between the two methods.

Recommended for SQL ServerSet instead of select to assign values to variables. When the expression returns a value and assigns a value to a variable, the set method is recommended.

The following table listsThe difference between set and select.

 

set

select

assign values to multiple variables at the same time

not supported

supported

when multiple values are returned by an expression

error

assign the last returned value to the variable

The expression does not return a value.

Variable assignedNull Value

Keep original variable value

The following is an example to illustrate the problem:

Create Table chinadba_a (
Userid int,
ADDR varchar (128)
)
Go
Insert into chinadba_a (userid, ADDR) values (1, 'addr1 ')
Insert into chinadba_a (userid, ADDR) values (2, 'addr2 ')
Insert into chinadba_a (userid, ADDR) values (3, 'addr3 ')
Go

When the expression returns multiple values, useSet assignment

Declare @ ADDR varchar (128)
Set @ ADDR = (select ADDR from chinadba_a)

/*
Error message: SERVER: Message 512, level 16, status 1, Row 2. The subquery returns more than one value. When the subquery follows in = ,! =, <, <=,>,> =, Or use a subquery as an expression.
*/
Go

When the expression returns multiple values, useSelect assignment

Declare @ ADDR varchar (128)
Select @ ADDR = ADDR from chinadba_a
Print @ ADDR--Value of the last ADDR column in The result set
--Result: addr3
Go

When the expression does not return a value, useSet assignment

Declare @ ADDR varchar (128)
Set @ ADDR = 'initial Value'
Set @ ADDR = (select ADDR from chinadba_a where userid = 4)
Print @ ADDR-- NullValue
Go

When the expression does not return a value, useSelect assignment

Declare @ ADDR varchar (128)
Set @ ADDR = 'initial Value'
Select @ ADDR = ADDR from chinadba_a where userid = 4
Print @ ADDR--Keep Original Value
Go

Note that,Select can also assign the value of the scalar query to the variable. If the scalar query does not return a value, the variable is set to null. In this case, the concept of scalar quantum query, which is identical to the use of set assignment, should be unfamiliar to everyone. For example, we can illustrate

declare @ ADDR varchar (128)
set @ ADDR = 'initial value '
-- select ADDR from chinadba_a where userid = 4 scalar query statement
select @ ADDR = (select ADDR from chinadba_a where userid = 4)
Print @ ADDR -- null value
go

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.