使用游标时使用WHILE循环的范例:
------------------------------------------------------------
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
--使用WHILE实现循环
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
+ @tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
------------------------------------------------------------
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
--使用WHILE实现循环
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
+ @tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
set @i=0
while @i<10
begin
print @i
set @i=@i+1
end
------------------------------------------------------------
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
--使用WHILE实现循环
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
+ @tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor