假设我有以下简单的表变量:
declare @databases table
(
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
-- insert a bunch rows into @databases
如果我想遍历各行,声明和使用游标是我唯一的选择吗?还有别的办法吗?
假设我有以下简单的表变量:
declare @databases table
(
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
-- insert a bunch rows into @databases
如果我想遍历各行,声明和使用游标是我唯一的选择吗?还有别的办法吗?
当前回答
SELECT @pk = @pk + 1
会更好:
SET @pk += @pk
避免使用SELECT如果你没有引用表,只是分配值。
其他回答
像这样定义临时表-
declare @databases table
(
RowID int not null identity(1,1) primary key,
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
-- insert a bunch rows into @databases
然后这样做——
declare @i int
select @i = min(RowID) from @databases
declare @max int
select @max = max(RowID) from @databases
while @i <= @max begin
select DatabaseID, Name, Server from @database where RowID = @i --do some stuff
set @i = @i + 1
end
Step1:下面的select语句为每条记录创建一个具有唯一行号的临时表。
select eno,ename,eaddress,mobno int,row_number() over(order by eno desc) as rno into #tmp_sri from emp
步骤2:声明所需的变量
DECLARE @ROWNUMBER INT
DECLARE @ename varchar(100)
步骤3:从临时表中获取总行数
SELECT @ROWNUMBER = COUNT(*) FROM #tmp_sri
declare @rno int
Step4:根据在temp中创建的唯一行号循环temp表
while @rownumber>0
begin
set @rno=@rownumber
select @ename=ename from #tmp_sri where rno=@rno **// You can take columns data from here as many as you want**
set @rownumber=@rownumber-1
print @ename **// instead of printing, you can write insert, update, delete statements**
end
如果您别无选择,只能逐行创建FAST_FORWARD游标。它和构建while循环一样快,而且更容易长期维护。
FAST_FORWARD 指定一个启用性能优化的FORWARD_ONLY、READ_ONLY游标。如果还指定了SCROLL或FOR_UPDATE,则FAST_FORWARD不能指定。
我是这样做的:
declare @RowNum int, @CustId nchar(5), @Name1 nchar(25)
select @CustId=MAX(USERID) FROM UserIDs --start with the highest ID
Select @RowNum = Count(*) From UserIDs --get total number of records
WHILE @RowNum > 0 --loop until no more records
BEGIN
select @Name1 = username1 from UserIDs where USERID= @CustID --get other info from that row
print cast(@RowNum as char(12)) + ' ' + @CustId + ' ' + @Name1 --do whatever
select top 1 @CustId=USERID from UserIDs where USERID < @CustID order by USERID desc--get the next one
set @RowNum = @RowNum - 1 --decrease count
END
没有游标,没有临时表,没有额外的列。 USERID列必须是唯一的整数,就像大多数主键一样。
以下是我的做法:
Select Identity(int, 1,1) AS PK, DatabaseID
Into #T
From @databases
Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1
While @pk <= @maxPK
Begin
-- Get one record
Select DatabaseID, Name, Server
From @databases
Where DatabaseID = (Select DatabaseID From #T Where PK = @pk)
--Do some processing here
--
Select @pk = @pk + 1
End
[编辑]因为我可能在第一次读这个问题时跳过了“变量”这个词,这里是一个更新的回答…
declare @databases table
(
PK int IDENTITY(1,1),
DatabaseID int,
Name varchar(15),
Server varchar(15)
)
-- insert a bunch rows into @databases
--/*
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MainDB', 'MyServer'
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MyDB', 'MyServer2'
--*/
Declare @maxPK int;Select @maxPK = MAX(PK) From @databases
Declare @pk int;Set @pk = 1
While @pk <= @maxPK
Begin
/* Get one record (you can read the values into some variables) */
Select DatabaseID, Name, Server
From @databases
Where PK = @pk
/* Do some processing here */
/* ... */
Select @pk = @pk + 1
End