title: SQL Server Stored Procedure with Cursor causing error
body: <p>I get this error message. Can anyone help me to fix my query? What is the right way to use the cursor?</p>
  
  <p>This stored procedure should fetch data from a temp table and insert it into two different tables. Some of the columns in the temp table like <code>EID, FN, MN, LN</code> should be inserted into table <code>dbo.tblUser</code>, and the rest of the columns from the temp table should be in the table <code>dbo.refRoster_Details</code>.</p>
  
  <p>Please do help me on this matter thanks.</p>
  
  <blockquote>
    <p>Msg 16950, Level 16, State 2, Line 22<br>
    The variable '@MyCursor' does not currently have a cursor allocated to it.</p>
    
    <p>Msg 16917, Level 16, State 2, Line 96<br>
    Cursor is not open.</p>
    
    <p>Msg 16917, Level 16, State 1, Line 227<br>
    Cursor is not open.</p>
  </blockquote>
  
  <p>Code:</p>
  
  <pre><code>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 &gt; 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
  </code></pre>