SQL Server traversal tables are typically used in cursors, and SQL Server can easily loop through a cursor to implement SQL Server traversal of records in a table. This article will introduce the use of table variables and cursors to implement the traversal of tables in the database.
table variables to implement the traversal of the table
In the following code, the differences between the blocks of code have been marked with a gray background.
Copy Code code as follows:
DECLARE @temp TABLE
(
[id] INT IDENTITY (1, 1),
[Name] VARCHAR (10)
)
DECLARE @tempId INT,
@tempName VARCHAR (10)
INSERT into @temp
VALUES (' a ')
INSERT into @temp
VALUES (' B ')
INSERT into @temp
VALUES (' C ')
INSERT into @temp
VALUES (' d ')
INSERT into @temp
VALUES (' e ')
While EXISTS (SELECT [id]
From @temp)
BEGIN
SET RowCount 1
SELECT @tempId = [id],
@tempName = [Name]
From @temp
SET ROWCOUNT 0
--delete from @temp where [id] = @tempId
PRINT ' Name:----' + @tempName
End
But this method must be aided by rowcount. However, using SET rowcount may affect the DELETE, INSERT, and UPDATE statements.
So modify the above while loop and use top to select the first record.
Copy Code code as follows:
While EXISTS (SELECT [id]
From @temp)
BEGIN
SELECT Top 1
@tempId = [id],
@tempName = [Name]
From @temp
DELETE from @temp
WHERE [id] = @tempId
SELECT *
From @temp
EXEC (' drop table ' +)
PRINT ' Name:----' + @tempName
End
There is also a problem with this approach, which needs to be removed from the traversed rows, in fact, we may not want to go through a row and delete a row in the actual application.
Using Cursors to traverse tables
Cursors are a very evil kind of existence, using cursors is often 2-3 times slower than using a set-oriented approach, which increases when the cursor is defined in large amounts of data. If possible, use while, subqueries, temporary tables, functions, table variables, and so on to replace the cursor, remembering that the cursor is always your last choice, not the first.
Copy Code code as follows:
--Define table variable
DECLARE @temp table
(
[id] INT IDENTITY (1, 1),
[name] VARCHAR
)
DECLARE @tempId INT,
@tempName VARCHAR (a)
DECLARE test_cursor Cursor, LOCA L for
SELECT [id],[name] from @temp
-Insert data value
INSERT into @temp
values (' a ')
insert INTO @te MP
Values (' B ')
INSERT into @temp
values (' C ')
Insert to @temp
values (' d ')
in SERT into @temp
VALUES (' e ')
--Open the cursor
Open test_cursor
while @ @FETCH_STATUS = 0
BEGIN
FETCH NEXT from Test_cursor to @tempId, @tempname
PRINT ' Name:----' + @tempName
End
Close Test_cursor
Deallocate test_cursor
