我收到了这条错误信息。有人能帮我修复我的查询吗?使用光标的正确方法是什么?
这个存储过程应该从临时表中获取数据,并将其插入两个不同的表中。临时表中的一些列(如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号线 光标未打开。
代码:
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发布于 2018-07-30 16:53:45
..。声明@MyCursor游标打开@MyCursor集@MyCursor =游标FAST_FORWARD用于.
在将游标分配给OPEN之前调用@MyCursor。在OPEN @MyCursor后面移动SET @MyCursor = CURSOR FAST_FORWARD FOR ...。
SET @MyCursor = CURSOR FAST_FORWARD FOR
...
ORDER BY [SAP ID] DESC
OPEN @MyCursor但是,我想知道为什么您会在SELECT TOP 1查询中使用游标。只有在发现数据时才能处理SELECT @var = col ...和ID @@rowcount = 1也可以。
https://stackoverflow.com/questions/51593020
复制相似问题