IF OBJECT_ID('[kkd].[proc_kkd_GetAutoExamineBid]') IS NOT NULL

    BEGIN

        DROP PROC [kkd].[proc_kkd_GetAutoExamineBid]

    END

GO

CREATE PROC [kkd].[proc_kkd_GetAutoExamineBid]

AS

    BEGIN

        --SELECT  *

        --FROM    ( SELECT    Bid ,

        --                    STUFF(( SELECT  ',' + CAST(B.Bid AS VARCHAR)

        --                            FROM    lending.Lending AS B

        --                            WHERE   B.ClientIndenNo = A.ClientIndenNo

        --                                    AND B.[Status] = 2

        --                                    AND B.Bid <> A.Bid

        --                          FOR

        --                            XML PATH('')

        --                          ), 1, 1, '') AS OldBid

        --          FROM      sign.Sign AS A

        --          WHERE     A.Status = 16

        --                    AND BranchKey IN ( 'SUBSIDIARY/KAKADAI/KAKADAI',

        --                                       'SUBSIDIARY/KAKADAI/KAKADAI7TIAN' )

        --        ) AS C

        --WHERE   C.OldBid IS NOT NULL

        DECLARE @Table1 TABLE

            (

              bid INT ,

              ClientIndenNo VARCHAR(50)

            )

        DECLARE @Table2 TABLE

            (

              bid INT ,

              ClientIndenNo VARCHAR(50)

            )

        INSERT  INTO @Table1

                ( bid ,

                  ClientIndenNo

                )

                SELECT  Bid ,

                        A.ClientIndenNo

                FROM    sign.Sign AS A

                WHERE   A.Status = 16

                        AND BranchKey IN ( 'SUBSIDIARY/KAKADAI/KAKADAI',

                                           'SUBSIDIARY/KAKADAI/KAKADAI7TIAN' )

        INSERT  INTO @Table2

                ( bid ,

                  ClientIndenNo 

                )

                SELECT  B.Bid ,

                        B.ClientIndenNo

                FROM    lending.Lending AS B

                WHERE   B.ClientIndenNo IN ( SELECT ClientIndenNo

                                             FROM   @Table1 )

                        AND B.[Status] = 2

        SELECT  *

        FROM    ( SELECT    Bid ,

                            STUFF(( SELECT  ',' + CAST(B.Bid AS VARCHAR)

                                    FROM    @Table2 AS B

                                    WHERE   B.ClientIndenNo = A.ClientIndenNo

                                  FOR

                                    XML PATH('')

                                  ), 1, 1, '') AS OldBid

                  FROM      @Table1 AS A

                ) AS C

        WHERE   C.OldBid IS NOT NULL

    END

GO