How can I simulate a cursor in a function in SQL SERVER?

If there is such a master-slave table, table 1 represents basic information, and table 2 represents extended information. For example, the master table represents the basic information of purchasers. To simplify the process, all the field names are in Chinese, there are two columns, one is the serial number, the other is the name, and the other is the purchase details. here only the Item Name field is listed. The two tables are as follows:

1. Buyer Information

Serial number	Name
1	Zhang San
2	Li Si

2. purchase records

Serial number	Number of purchasers	Item Name
1	1	Print paper
2	1	Computer
3	2	Clothes
4	2	Mobile phone

The "Serial Number" field is of the int type and is the primary key, so it cannot be repeated. In table 2, the buyer's serial number field corresponds to the serial number in Table 1. Now we want to query the buyer's information, by the way, the content of his purchase is displayed at the end, and "," is used to separate the content of each purchase, that is, to obtain a view, as shown below:

Serial number	Name	Item Summary
1	Zhang San	Print paper, computer
2	Li Si	Clothes, mobile phones

Because you want to get a view, you cannot display it in the storage process. You only need to use functions. Of course, you can also query the purchased items one by one on the client based on the query results, however, multiple round-trips to the C/S end will affect the speed of creating a view. Creating a view is undoubtedly the best method. However, only functions in the view can be used, but the function does not support cursors, what should we do? In my work, I used the following method to simulate a "cursor" to achieve the effect (it is easier to use C # To write extended memory in SQL Server 2005, this issue is not discussed here ):

Create Function [DBO]. [fgetgoodnamesbyopid] (
@ Opid int -- number of purchasers
)
Returns nvarchar (2000) -- query results separated by commas (,)
As
Begin
Declare @ A nvarchar (2000)
Set @ A =''
Declare @ curid int
Declare @ curname nvarchar (20)

Set @ curid =-1
Set @ curname =''
While (@ curname is not null)
Begin
Set @ curname = NULL

-- The key is the following sentence: each time a record is queried and sorted by serial number, it is actually a loop through the serial number.
Select top 1 @ curid = No., @ curname = item name from procurement information where purchaser No. = @ opid
And no.> @ curid order by no.

If @ curname is not null
Begin
If Len (@ A)> 0
Set @ A = @ A + ','
Set @ A = @ A + @ curname
End

End
 
Return @
End

With the above function, you can use the following query statement to create a view.

Select *, DBO. fgetgoodnamesbyopid (serial number) from buyer Information

Although the above method is stupid and fast, it can solve the problem and I hope it will help you.