select 查询数据传入存储过程，用游标循环 ，再插入临时表 join 关联

USE [His_Data_From_All_Pay_Ora]
GO
/****** Object:  StoredProcedure [dbo].[SP_CARD_MONEY_4WORKER_NEWCard2023]    Script Date: 2023-03-24 11:13:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        
-- Create date: 
-- Description:    
-- =============================================
ALTER PROCEDURE [dbo].[SP_CARD_M]
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    
    DECLARE @CardNodnumber TABLE (CardNo VARCHAR(20))

    INSERT INTO @CardNodnumber (CardNo)
    SELECT a.card_no 
    FROM dbo.tbl_sell a
    join (
    SELECT  ID_NO  as 身份证
    FROM OPENQUERY(ORACLE,'select a.card_no from zy_ca_acc a,zy_wor_info b,zy_wor_info c where a.card_no=b.opcard_no and b.op_no=c.op_no and c.dept_no=87 and is_bt=0 order by employee_name') z, gj_center.dbo.交 y 
    WHERE z.op_no=y.职工编号 and y.退休标志<>'(退休卡)') b on a.ID_NO=b.身份证 and a.CARD_TYPE='0014';

    DECLARE @CardNo VARCHAR(20)

    --创建临时表存放tsql数据
    CREATE TABLE #temp_table (card_no1  VARCHAR(20), name VARCHAR(20),str1 VARCHAR(20),str2 VARCHAR(20), str3 VARCHAR(20), balance VARCHAR(20))

    --游标自动下一个查询
    DECLARE CardNo_cursor CURSOR FOR 
    SELECT CardNo FROM @CardNodnumber


    OPEN CardNo_cursor

    FETCH NEXT FROM CardNo_cursor INTO @CardNo

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用存储过程获取查询结果 将查询结果插入临时表
        INSERT INTO #temp_table ( card_no1 , name , str1 , str2,str3, balance)
        EXEC Work_Platform_4UPTIC.dbo.sp_Query_IC_Card_Trade_Related_Data_From_ALL_PAY_ORACLE 'CARD_ACC',@CardNo
        
        FETCH NEXT FROM CardNo_cursor INTO @CardNo
    END

    CLOSE CardNo_cursor
    --释放游标
    DEALLOCATE CardNo_cursor

    -- 在临时表中筛选字段
    SELECT CARD_NO1 AS 卡 ,a.NAME AS 姓 , b.ID_NO as 身份证, BALANCE AS 余额  FROM #TEMP_TABLE a
    JOIN ard_sl b ON a.card_no1 = b.card_no
    ORDER BY a.NAME

    DROP TABLE #TEMP_TABLE
END