manipulate array of data in SQL

最新推荐文章于 2024-09-20 19:49:02 发布

原创最新推荐文章于 2024-09-20 19:49:02 发布 · 279 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

本文介绍了一种在SQL Server中模拟数组处理的方法，通过使用表变量存储数据，并结合索引进行迭代访问，以实现类似数组的操作。这种方法避免了使用速度较慢的游标。

As we know, SQL server lacks of concept of array.

a workaround is to store the data in a table variable and then iterate it.

one way to do iterate is using cursor, but it's slow

another way is add a id column in the table variable, and then access the record in table variable with index.

DECLARE @UserName VARCHAR(30), @DisplayName VARCHAR(30)

DECLARE @Users TABLE(Id int identity(1,1), UserName VARCHAR(30), DisplayName VARCHAR(30))
INSERT INTO @Users
VALUES
('lxing', 'Lili')
,('ashan', 'Allen')

DECLARE @RowsToProcess int = @@ROWCOUNT
DECLARE @Row INT=1

WHILE(@Row <= @RowsToProcess)

BEGIN

SELECT @UserName = UserName, @DisplayName = DisplayName FROM @Users where Id = @Row

declare @uid int

--insert user

SET @Row = @Row + 1
END