Replace cursors and while Loops

Generally, you will choose cursor when dealing with loop problems, because it is very convenient to use and the code is well implemented. However, Using cursor can cause performance problems. So is there any good alternative? This article is useful for reference (replacingcursors ).
And while loops), rewrite cursor performance increased several times.

 

Therefore, when designing code, we can consider whether it can be implemented without using cursor. The source code and the modified Code are as follows:

 

-- The source code first queries the r query @ tmp_id, then obtains tmp_values with the data @ tmp_id, and then updates the value of output_tbl in the table.

Declare temp_cursor
Cursor

Select column_data

From dB. DBO. many_tbl

Where ID
= @ Tmp_id -- Key Data

Order by column_data

 

Open temp_cursor

 

Fetch next
From temp_cursor

 

Into @ tmp_data

 

While
@ Fetch_status = 0

 

Begin

Select @ tmp_values
= @ Tmp_values +
Convert (varchar (20), @ tmp_data)
+ ','

 

Fetch next
From temp_cursor

Into @ tmp_data

End

 

Close temp_cursor

Deallocate temp_cursor

 

Update dB. DBO. output_tbl

Set column_out
= @ Tmp_values

Where ID
= @ Tmp_id

 

-- Replace the code (fast and concise)

 

Create Function DBO. ufn_data_pivot (@ ID
As INT)

Returns varchar (20)

As

Begin

Declare @ Value
Varchar (20)

Set @ Value
=''

Select @ Value
= @ Value + convert (varchar (20), column_data)
+ ','

From dB. DBO. many_tbl

Where id = @ ID

Order by column_data

 

Return @ Value

End

 

Update dB. DBO. output_tbl

Set column_out = DBO. ufn_data_key( key_column)