Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the method of SQLServer variable assignment". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What are the methods of SQLServer variable assignment

The difference between the two SQLServer variable assignment methods has been explained in detail in SQLServer Books online, but most of the time we have not noticed, in fact, there are still many differences between the two ways.

SQLServer recommends using SET instead of SELECT to assign values to variables. The SET method is recommended when an expression returns a value and assigns a variable.

Here is a concrete example to illustrate the problem:

Createtablechinadba1 (

Useridint

Addrvarchar (128)

)

Go

Insertintochinadba1 (userid,addr) values (1meme addr1')

Insertintochinadba1 (userid,addr) values (2) addr2')

Insertintochinadba1 (userid,addr) values (3) addr3')

Go

Use SET assignment when an expression returns multiple values

Declare@addrvarchar (128)

Set@addr= (selectaddrfromchinadba1)

/ *

-- error message is

What are the methods of SQLServer variable assignment

Server: message 512, level 16, status 1, line 2

The subquery returns more than one value. This is not allowed when the subquery follows =,! =, =, or when the subquery is used as an expression.

* /

Go

When an expression returns multiple values, use SELECT to assign declare@addrvarchar (128)

Select@addr=addrfromchinadba1

The value of the last addr column in the print@addr-- result set

-result: addr3

Go

Use SET to assign declare@addrvarchar (128) when the expression does not return a value

Set@addr=' initial value'

Set@addr= (selectaddrfromchinadba1whereuserid=4)

Print@addr-- null value

Go

Use SELECT to assign declare@addrvarchar (128) when the expression does not return a value

Set@addr=' initial value'

Select@addr=addrfromchinadba1whereuserid=4

Keep the original value of print@addr--

Go

Note that SELECT can also assign the value of a scalar quantum query to a variable, and if the scalar quantum query does not return a value, the variable is set to a null value.

This is exactly the same as using SET assignment.

This is the end of the content of "the method of SQLServer variable assignment". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.