[Network Abstract] the effect of set and select on variable assignment in dynamic SQL statements

In dynamic SQL, there are many ways to assign values to character variables. among them, set and select use the most friends. for birds like me, they may only use these two methods and do not really understand the true meaning of these two methods. my personal understanding is as follows: Set only assigns a value to a character variable, which can be a specific string or a query result, however, the result must be a unique record with only one field. The select method has a large function, it can assign all values of a column in a multi-row record of a query to a string variable (such as @ SQL. before assembling the @ SQL variable, select all the rows of interest in the table, and then the system automatically links the values in these rows to @ SQL to assemble the strings.

For example:

Declare @ TB table (A varchar (10), B varchar (10 ))
Insert into @ TB values ('A', '1 ')
Insert into @ TB values ('B', '2 ')
Insert into @ TB values ('C', '3 ')
Insert into @ TB values ('D', '4 ')
Insert into @ TB values ('E', '5 ')

Declare @ SQL varchar (1000)
Select @ SQL = 'A'
Select @ SQL = @ SQL + ',' +

From @ TB
-- Order by a DESC

Print @ SQL

 

To better prove this, sort the records in order. according to the understanding of the landlord, if only one record is selected at a time, the sorting section has no effect on the output result, c, d, e ", but the result is" A, E, D, C, B, a ", so it can be proved that string assembly appears after query, is to query the records that meet the conditions are selected, and then follow the link from start to end to the character variable @ SQL.

This article from the csdn blog, reprinted Source: http://blog.csdn.net/acupofnescafe/archive/2008/08/06/2774676.aspx