SQLServer数据库循环表操作每一条数据（游标的使用）

DECLARE @FunctionCode VARCHAR(20)--声明游标变量
DECLARE curfuntioncode CURSOR FOR SELECT FunctionalityCode FROM dbo.SG_Functionality WHERE Type=2 ORDER BY TimeStamp --创建游标
OPEN curfuntioncode --打开游标
FETCH NEXT FROM curfuntioncode INTO @FunctionCode --给游标变量赋值
WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行执行成功
BEGIN
PRINT @FunctionCode  --打印数据（对每一行数据进行操作）
FETCH NEXT FROM curfuntioncode INTO @FunctionCode --下一个游标变量赋值
END
CLOSE curfuntioncode --关闭游标
DEALLOCATE curfuntioncode --释放游标

 

示例

CREATE PROC USP_AddPermissions_Admin ( @usercode NVARCHAR(50) ) AS

DECLARE @UserID UNIQUEIDENTIFIER

BEGIN

SET @UserID=(SELECT UserID FROM dbo.SG_User WHERE UserCode=@usercode)

DECLARE @FunctionCode VARCHAR(20)

DECLARE curfuntioncode CURSOR FOR SELECT FunctionalityCode FROM dbo.SG_Functionality WHERE Type=2 ORDER BY TimeStamp

OPEN curfuntioncode FETCH NEXT FROM curfuntioncode INTO @FunctionCode

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO SG_UserFunctionality (UserFunctionalityID,UserID,FunctionalityCode,Type)VALUES(NEWID(),@UserID,@FunctionCode ,2)

FETCH NEXT FROM curfuntioncode INTO @FunctionCode

END

CLOSE curfuntioncode

DEALLOCATE curfuntioncode

END

转载于:https://www.cnblogs.com/heyiping/p/9617925.html