TSQL 带参数的游标 表值类型+record + cursor 模仿 plsql %rowtype

SET NOCOUNT ON;
IF EXISTS(
       SELECT 1
       FROM   SYS.types AS t
       WHERE  t.name = 'a01_A01_NODE_R_TABLE_TYPE'
   )
    DROP TYPE dbo.a01_A01_NODE_R_TABLE_TYPE
GO
CREATE TYPE dbo.a01_A01_NODE_R_TABLE_TYPE AS  TABLE 
(
    ID NUMERIC(10),
    A00 NVARCHAR(36)  NOT NULL,
    A0102 NVARCHAR(11) NOT NULL,
    A0104 NVARCHAR(11) NOT NULL
    PRIMARY KEY CLUSTERED(ID)
)
GO
IF EXISTS(
       SELECT 1
       FROM   SYS.types AS t
       WHERE  t.name = 'USR_NODE_TABLE_TYPE'
   )
    DROP TYPE dbo.USR_NODE_TABLE_TYPE
GO
CREATE TYPE dbo.USR_NODE_TABLE_TYPE AS  TABLE 
(
    ID NUMERIC(10)  ,
    LOGON_NAME NVARCHAR(11) NOT NULL,
    NodeID NVARCHAR(36) NOT NULL,
    NodeNAME NVARCHAR(36) NOT NULL
    PRIMARY KEY CLUSTERED(ID)
)
GO
DECLARE @PEOPLE_SQL        NVARCHAR(500) = N'
		SELECT ROW_NUMBER() OVER( ORDER BY A00) AS ID,* FROM (
		SELECT DISTINCT  A.A00,
				A.A0102,
				A.A0104
		FROM   A01 A
				INNER JOIN A01_Node_R R
					ON  R.A00 = A.A00
		WHERE  R.NodeID = @NODEID
		) AS T1	',
        @NODE_SQL        NVARCHAR(500) = N'
		SELECT ROW_NUMBER() OVER( ORDER BY LOGON_NAME) AS ID,T1.* FROM (
			SELECT DISTINCT BU.LOGON_NAME,
				   ZNT.NodeID,
				   ZNT.Name
			FROM   BAP_USER BU
				   INNER JOIN Z_NodeTree AS znt
						ON  znt.NodeID = BU.NodeID 
		) AS T1',      
		@PEOPLE_ROWID NUMERIC(10) = 1,
        @PEOPLE_ROMAX NUMERIC(10) = 0,    
        @_NODEID UNIQUEIDENTIFIER    ,
        @PEOPLE_RECORD        a01_A01_NODE_R_TABLE_TYPE,
        @PEOPLE_CURSOR     a01_A01_NODE_R_TABLE_TYPE, 
        
        @NODE_ROWID NUMERIC(10) = 1,
        @NODE_MAX  NUMERIC(10) = 0,
        @NODE_RECORD        USR_NODE_TABLE_TYPE,
        @NODE_CURSOR     USR_NODE_TABLE_TYPE
INSERT INTO @NODE_CURSOR EXEC SYS.sp_executesql @STMT=@NODE_SQL
SET @NODE_MAX=@@ROWCOUNT
WHILE (@NODE_ROWID<@NODE_MAX)
BEGIN 
	DELETE FROM @NODE_RECORD/*清空*/
	DELETE FROM @NODE_CURSOR OUTPUT DELETED.* INTO @NODE_RECORD WHERE ID=@NODE_ROWID/*取一条*/
	SELECT @_NODEID=NODEID FROM @NODE_RECORD/*赋值*/
	INSERT INTO @PEOPLE_CURSOR EXEC SYS.sp_executesql @STMT=@PEOPLE_SQL,
	@PARAMS=N'@NODEID  UNIQUEIDENTIFIER',@NODEID=@_NODEID/*结果敀到 table type 变量，模拟带参数游标*/
	SET @PEOPLE_ROMAX=@@ROWCOUNT/*记录数*/
	SET @PEOPLE_ROWID=1/*内循环*/	
	WHILE (@PEOPLE_ROWID<=@PEOPLE_ROMAX)
	BEGIN
		DELETE FROM @PEOPLE_RECORD
		DELETE FROM @PEOPLE_CURSOR OUTPUT DELETED.* INTO @PEOPLE_RECORD WHERE ID=@PEOPLE_ROWID
		PRINT @PEOPLE_ROWID
		
		--SELECT * FROM @@PEOPLE_RECORD
		SET @PEOPLE_ROWID+=1
	END
	--SELECT * FROM @NODE_RECORD
	SET @NODE_ROWID+=1
END