首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >带有游标导致错误的Server存储过程

问带有游标导致错误的Server存储过程
EN

Stack Overflow用户
提问于 2018-07-30 11:30:40
回答 1查看 1.6K关注 0票数 1

我收到了这条错误信息。有人能帮我修复我的查询吗?使用光标的正确方法是什么?

这个存储过程应该从临时表中获取数据,并将其插入两个不同的表中。临时表中的一些列(如EID, FN, MN, LN )应该插入到表dbo.tblUser中,而临时表中的其余列应该在表dbo.refRoster_Details中。

请在这件事上帮助我,谢谢。

Msg 16950,16级,状态2,22号线 变量“@MyCursor”目前没有为其分配游标。 Msg 16917,16级,状态2,96号线 光标未打开。 Msg 16917,16级,状态1,227号线 光标未打开。

代码:

代码语言:javascript
复制
CREATE PROCEDURE [dbo].[sp_BulkUserInsert]
AS
BEGIN
    SET NOCOUNT ON;

    OPEN SYMMETRIC KEY SymmetricKey1
    DECRYPTION BY CERTIFICATE Certificate1;

        DECLARE @vEID varchar(max) 
        DECLARE @vFirstName varchar(max) 
        DECLARE @vMiddleName varchar(max)
        DECLARE @vLastName varchar(max) 
        DECLARE @vRoleID int 
        DECLARE @vLevelID int 
        DECLARE @vEmployeeTypeID int 
        DECLARE @vBadgeNo int 
        DECLARE @vSAPID int 
        DECLARE @vContactNo int 
        DECLARE @vBirthDate date 
        DECLARE @vProjectID int 
        DECLARE @vManagerID int 
        DECLARE @vSupervisorID int 
        DECLARE @vLocationID int 
        DECLARE @vHireDate date 
        DECLARE @vRollIn date 
        DECLARE @vRollOff date 
        DECLARE @vTimeIn datetime 
        DECLARE @vTimeOut datetime 
        DECLARE @vIsVisa int 
        DECLARE @vIsPassport int 
        DECLARE @vDateAdded date 
        DECLARE @vDateModified date 
        DECLARE @vTowerID int 
        DECLARE @vIsActive int 

            DECLARE @MyCursor CURSOR
            OPEN @MyCursor
            SET @MyCursor = CURSOR FAST_FORWARD FOR


                SELECT TOP 1
                    --columns to push in tblUser
                    EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, EID)),
                    EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar,FIRSTNAME)),
                    EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar,MIDDLENAME)),
                    EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar,LASTNAME)),
                    --columns to push in roster_detail
                    CAST(ISNULL(ROLEID,'4') as int) ROLEID,
                    CAST(ISNULL(LEVELID,'1') as int) LEVELID,
                    CAST(ISNULL(EMPLOYEETYPEID,'1') as int) EMPLOYEETYPEID,
                    CAST(ISNULL(BADGEno,'1') as int) BADGEno,
                    CAST(ISNULL([SAP ID],'1') as int) [SAP ID],
                    CAST(ISNULL(CONTACTno,'1') as int) CONTACTno,
                    CAST(ISNULL(BIRTHDAY,'1991-01-01') as date) BIRTHDAY,
                    CAST(ISNULL(HIREDATE,'1991-01-01') as date) HIREDATE,
                    CAST(ISNULL(PROJECTID,'1') as int) PROJECTID,
                    CAST(ISNULL(MANAGERID,'1') as int) MANAGERID,
                    CAST(ISNULL(SUPERVISORID,'1') as int) SUPERVISORID,
                    CAST(ISNULL(LOCATIONID,'1') as int) LOCATIONID,
                    CAST(ISNULL(ROLLIN,'1991-01-01') as date) ROLLIN,
                    CAST(ISNULL(ROLLOFF,'1991-01-01') as date) ROLLOFF,
                    CAST(ISNULL(TIMEIN,'1991-01-01') as datetime) TIMEIN,
                    CAST(ISNULL([TIMEOUT] ,'1991-01-01') as datetime) [TIMEOUT],
                    CAST(ISNULL(ISVISA,'1') as int) ISVISA,
                    CAST(ISNULL(ISPASSPORT,'1') as int) ISPASSPORT,
                    CAST(ISNULL(DATEADDED,'1991-01-01') as date) DATEADDED,
                    CAST(ISNULL(DATEMODIFIED,'1991-01-01') as date) DATEMODIFIED,
                    CAST(ISNULL(TOWERID,'1') as int) TOWERID,
                    CAST(ISNULL(ISACTIVE,'1') as int) ISACTIVE
                FROM tblRIRO_TempRosterList
                ORDER BY [SAP ID] DESC

            --OPEN @MyCursor
           FETCH NEXT FROM @MyCursor INTO
            @vEID, 
            @vFirstName, 
            @vMiddleName, 
            @vLastName,
           @vRoleID,
           @vLevelID, 
           @vEmployeeTypeID, 
           @vBadgeNo, 
           @vSAPID, 
           @vContactNo, 
           @vBirthDate, 
           @vProjectID, 
           @vManagerID, 
           @vSupervisorID, 
           @vLocationID, 
           @vHireDate, 
           @vRollIn, 
           @vRollOff, 
           @vTimeIn, 
           @vTimeOut, 
           @vIsVisa, 
           @vIsPassport, 
           @vDateAdded, 
           @vDateModified, 
           @vTowerID, 
           @vIsActive

           WHILE @@FETCH_STATUS = 0
           BEGIN
                PRINT(@vEID)
                DECLARE @IsEID_Exist int = ISNULL((SELECT TOP 1 COUNT(*) FROM tblUser WHERE EID_encrypt = @vEID),0)
                PRINT (@IsEID_Exist)
                DECLARE @Incremental_UserID int = 0
                IF (@IsEID_Exist = 0)
                BEGIN
                    INSERT INTO tblUser (EID_encrypt, FirstName_encrypt, MiddleName_encrypt, LastName_encrypt)
                    VALUES (
                            EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vEID)),
                            EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vFirstName)),
                            EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vMiddleName)),
                            EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vLastName)))
                    SELECT TOP 1 @Incremental_UserID = @@IDENTITY
                    IF (@Incremental_UserID > 0) 
                    BEGIN
                        INSERT INTO refRoster_Detail (
                                    UserID, 
                                    RoleID, 
                                    LevelID,
                                    EmployeeTypeID,
                                    BadgeNo_encrypt,
                                    SAPID_encrypt,
                                    ContactNo_encrypt,
                                    BirthDate_encrypt,
                                    ProjectID,
                                    ManagerID,
                                    SupervisorID,
                                    LocationID,
                                    HireDate,
                                    RollIn,
                                    RollOff,
                                    TimeIn,
                                    [TimeOut],
                                    IsVisa,
                                    IsPassport,
                                    DateAdded,
                                    DateModified,
                                    TowerID,
                                    IsActive)
                        VALUES(@Incremental_UserID, 
                                @vRoleID, 
                                @vLevelID, 
                                @vEmployeeTypeID, 
                                EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vBadgeNo)), 
                                EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vSAPID)), 
                                EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vContactNo)), 
                                EncryptByKey (Key_GUID('SymmetricKey1'), CONVERT(varchar, @vBirthDate)), 
                                @vProjectID, 
                                @vManagerID, 
                                @vSupervisorID, 
                                @vLocationID, 
                                @vHireDate, 
                                @vRollIn, 
                                @vRollOff, 
                                @vTimeIn, 
                                @vTimeOut, 
                                @vIsVisa, 
                                @vIsPassport, 
                                @vDateAdded, 
                                @vDateModified, 
                                @vTowerID,
                                @vIsActive)
                    END
                END

                FETCH NEXT FROM @MyCursor INTO 
                   @vEID, 
                   @vFirstName, 
                   @vMiddleName, 
                   @vLastName,
                   @vRoleID,
                   @vLevelID, 
                   @vEmployeeTypeID, 
                   @vBadgeNo, 
                   @vSAPID, 
                   @vContactNo, 
                   @vBirthDate, 
                   @vProjectID, 
                   @vManagerID, 
                   @vSupervisorID, 
                   @vLocationID, 
                   @vHireDate, 
                   @vRollIn, 
                   @vRollOff, 
                   @vTimeIn, 
                   @vTimeOut, 
                   @vIsVisa, 
                   @vIsPassport, 
                   @vDateAdded, 
                   @vDateModified, 
                   @vTowerID, 
                   @vIsActive
           END
           CLOSE @MyCursor
           DEALLOCATE @MyCursor
    CLOSE SYMMETRIC KEY SymmetricKey1;
    END
EN

回答 1

Stack Overflow用户

发布于 2018-07-30 16:53:45

..。声明@MyCursor游标打开@MyCursor集@MyCursor =游标FAST_FORWARD用于.

在将游标分配给OPEN之前调用@MyCursor。在OPEN @MyCursor后面移动SET @MyCursor = CURSOR FAST_FORWARD FOR ...。

代码语言:javascript
复制
SET @MyCursor = CURSOR FAST_FORWARD FOR
...
    ORDER BY [SAP ID] DESC
OPEN @MyCursor

但是,我想知道为什么您会在SELECT TOP 1查询中使用游标。只有在发现数据时才能处理SELECT @var = col ...和ID @@rowcount = 1也可以。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51593020

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档