该SP使用了嵌套游标，因为里层的游标调用了另一个存储过程，而且该存储过程对里层游标查询造成了影响，所以，里层游标用了临时表

IF EXISTS(SELECT * FROM sysobjects WHERE type='P' AND name='usp_Copy_UserInfo_All')
DROP PROCEDURE usp_Copy_UserInfo_All
GO

create procedure usp_Copy_UserInfo_All
AS
BEGIN
DECLARE @PERSONID NVARCHAR(40)
DECLARE @NEWPERSONID NVARCHAR(40)
DECLARE @SUBCOUNT INT
CREATE TABLE #tempTable
(oid NVARCHAR(40))
SET @SUBCOUNT=0

DECLARE CURSOR_GET_PERSONID CURSOR
FOR

--找出所有主要身份
SELECT PERSONID FROM PSNACCOUNT WHERE JOBCHARACTER='41_01' AND EMPLOYEEID IN
(SELECT EMPLOYEEID FROM PSNACCOUNT WHERE JOBCHARACTER='41_02')
OPEN CURSOR_GET_PERSONID
FETCH NEXT FROM CURSOR_GET_PERSONID INTO @PERSONID
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @PERSONID 主要身份
    DELETE FROM #tempTable
    --找出该用户的兼职身份
 INSERT INTO #tempTable(OID) SELECT PERSONID FROM PSNACCOUNT WHERE EMPLOYEEID IN
 (SELECT EMPLOYEEID FROM PSNACCOUNT WHERE PERSONID=@PERSONID) AND JOBCHARACTER='41_02'

--这里定义里层游标 
DECLARE CURSOR_GET_NEW_PERSONID CURSOR
 FOR
    SELECT OID FROM #tempTable
 
 OPEN CURSOR_GET_NEW_PERSONID
 FETCH NEXT FROM CURSOR_GET_NEW_PERSONID INTO @NEWPERSONID
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @SUBCOUNT = @SUBCOUNT + 1
        SELECT Convert(NVARCHAR(10),@SUBCOUNT) + '__' + @NEWPERSONID
  EXEC usp_Copy_UserInfo @PERSONID,@NEWPERSONID
  FETCH NEXT FROM CURSOR_GET_NEW_PERSONID INTO @NEWPERSONID
  END

 CLOSE CURSOR_GET_NEW_PERSONID
 DEALLOCATE CURSOR_GET_NEW_PERSONID
 SET @SUBCOUNT=0
 
FETCH NEXT FROM CURSOR_GET_PERSONID INTO @PERSONID
END
 CLOSE CURSOR_GET_PERSONID
 DEALLOCATE CURSOR_GET_PERSONID

END