Sql Server 2008 游標的使用

                   DECLARE  @trancountAS int,            @dRowId     AS int,@dAvailRoleCd   AS varchar(20),@dRoleCdAS varchar(20),@dProgCd        AS varchar(20),@dGrantAddAS char(1),@dGrantUpdAS char(1),@dGrantDelAS char(1),@dGrantExpAS char(1),@dGrantPnt   AS char(1),@dGrantPwdAS char(1),@dCreateDateAS datetime,@dCreateUserAS bigint,        @dActionTypeAS VARCHAR(10),                        @dKeyValAS varchar(100) = NULL,@dStampDateAS datetime = GETDATE(),        @dNeedUpdAS int = 1;DECLARE @tbl TABLE (    wAvailRoleCd    varchar(20) NULL,wRoleCd    varchar(20) NULL,wProgCd    varchar(20) NULL,wGrantAddchar(1) NULL,wGrantUpdchar(1) NULL,wGrantDelchar(1) NULL,wGrantExpchar(1) NULL,wGrantPnt   char(1) NULL,wGrantPwd    char(1) NULL,wCreateUser    bigint  NULL);INSERT INTO @tbl(wAvailRoleCd,wRoleCd, wProgCd, wGrantAdd, wGrantUpd, wGrantDel, wGrantExp, wGrantPnt, wGrantPwd, wCreateUser)(SELECT    b.value('(wAvailRoleCd[not(@xsi:nil = "true")])[1]', 'varchar(20)') AS wAvailRoleCd,b.value('(wRoleCd[not(@xsi:nil = "true")])[1]', 'varchar(20)') AS wRoleCd,b.value('(wProgCd[not(@xsi:nil = "true")])[1]', 'varchar(20)') AS wProgCd,b.value('(wCanAdd[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantAdd,b.value('(wCanUpd[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantUpd,b.value('(wCanDel[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantDel,b.value('(wCanExp[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantExp,b.value('(wCanPnt[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantPnt,b.value('(wCanPwd[not(@xsi:nil = "true")])[1]', 'char(1)') AS wGrantPwd,b.value('(wCreateUser[not(@xsi:nil = "true")])[1]', 'bigint') AS wCreateUserFROM @pRoleProgsLst.nodes('//List/Item') AS a(b));--SELECT TOP 1 @dShipId = wShipId, @dCreateUser = wCreateUser FROM @tbl;SET @trancount = @@TRANCOUNT;IF @trancount = 0BEGIN TRANSACTION;ELSESAVE TRANSACTION spaUpdInsRoleProgs;     DECLARE C_DTL CURSOR FORSELECT wAvailRoleCd,wRoleCd, wProgCd, wGrantAdd, wGrantUpd, wGrantDel, wGrantExp, wGrantPnt, wGrantPwd, wCreateUser FROM @tbl;OPEN C_DTL;FETCH NEXT FROM C_DTL INTO  @dAvailRoleCd,@dRoleCd, @dProgCd, @dGrantAdd, @dGrantUpd, @dGrantDel, @dGrantExp, @dGrantPnt, @dGrantPwd, @dCreateUser;WHILE @@FETCH_STATUS = 0BEGINSET @dActionType = 'ADD';SET @dRowId = NULL;SELECT @dRowId = wRowId, @dCreateDate = ISNULL(wStampDate, wCreateDate) FROM [dbo].[mRoleProg] WHERE wRoleCd = @dAvailRoleCd AND wProgCd = @dProgCd;IF @pIsSyncQ = 0 AND @dRowId IS NOT NULL AND @dCreateDate < @pSyncDate SET @dStampDate = @pSyncDate;IF @pIsSyncQ = 0 AND @dRowId IS NOT NULL AND @dCreateDate > @pSyncDate SET @dNeedUpd = 0;IF @dNeedUpd > 0BEGINIF @dRowId IS NULLBEGININSERT INTO mRoleProg ( wRoleCd, wProgCd, wGrantAdd, wGrantUpd, wGrantDel, wGrantExp, wGrantPnt, wGranPwd, wCreateDate, wCreateUser)VALUES (@dAvailRoleCd, @dProgCd, @dGrantAdd, @dGrantUpd, @dGrantDel,@dGrantExp, @dGrantPnt, @dGrantPwd, @dStampDate, @dCreateUser);ENDELSEBEGIN   IF(@dRoleCd <> @dAvailRoleCd)   BEGINDELETE FROM mRoleProg WHERE wRoleCd = @dAvailRoleCd AND wProgCd = @dProgCd;   END   ELSE   BEGINUPDATE mRoleProg SET wRoleCd= ISNULL(@dAvailRoleCd, wRoleCd), wProgCd = ISNULL(@dProgCd, wProgCd), wGrantAdd = ISNULL(@dGrantAdd, wGrantAdd),wGrantUpd = ISNULL(@dGrantUpd, wGrantUpd),wGrantDel = ISNULL(@dGrantDel, wGrantDel),wGrantExp = ISNULL(@dGrantExp, wGrantExp),wGrantPnt = ISNULL(@dGrantPnt, wGrantPnt),wGranPwd = ISNULL(@dGrantPwd, wGranPwd),wStampUser = @dCreateUser,wStampDate = @dStampDateWHERE wRoleCd = @dAvailRoleCd AND wProgCd = @dProgCd;   ENDENDENDFETCH NEXT FROM C_DTL INTO @dAvailRoleCd,@dRoleCd, @dProgCd, @dGrantAdd, @dGrantUpd, @dGrantDel, @dGrantExp, @dGrantPnt, @dGrantPwd, @dCreateUser;ENDCLOSE C_DTL;DEALLOCATE C_DTL;