sqlserver 变量表应用 范例

-- =============================================
-- Description: <>
-- Author:  <>
-- Create date: <>
-- =============================================

CREATE PROCEDURE [dbo].[pro_get_parts_list]
 @IN_MODEL_CD1 NVARCHAR(5),    
 @IN_MODEL_CD2 NVARCHAR(10),    
    @IN_LOT INTEGER
AS

BEGIN

 SET NOCOUNT ON;

 DECLARE @CTMP_BLOCK_NO  INTEGER      
 DECLARE @CTMP_LINE_NO   INTEGER      
 DECLARE @CTMP_LINE_BRANCH_NO INTEGER 
 DECLARE @CTMP_DRAW_CD1 NVARCHAR(5)   
 DECLARE @CTMP_DRAW_CD2 NVARCHAR(10)  
 DECLARE @CTMP_ORDER_FLG NVARCHAR(1)  

 DECLARE @iCount INT=0
 DECLARE @iLoop INT=1
 
 DECLARE @TMP_TBL TABLE(
        ID INT IDENTITY(1,1)
       ,BLOCK_NO     INT NOT NULL
       ,LINE_NO  INT  NOT NULL
       ,LINE_BRANCH_NO INT  NOT NULL
       ,DRAW_CD1 NVARCHAR(5)
       ,DRAW_CD2 NVARCHAR(10)
       ,USE_QUANTITY  NUMERIC(15,4)
       ,LEVEL   INT 
       ,ORDER_FLG      TINYINT 
       ,ASSY_PLACE   TINYINT
       ,ASSYNO  NVARCHAR(8)
       ,FACTORY_KBN TINYINT
       ,DEL_PLACE NVARCHAR(20)
       ,SHOW_FLG TINYINT
                           )

 INSERT INTO @TMP_TBL
 SELECT
   BLOCK_NO
  ,LINE_NO
  ,LINE_BRANCH_NO
  ,NULL
  ,NULL
  ,NULL
  ,NULL
  ,NULL
  ,NULL
  ,NULL
  ,NULL
  ,NULL
  ,NULL
 FROM(
 SELECT
 T1.BLOCK_NO AS BLOCK_NO,
 T1.LINE_NO AS LINE_NO,
 T1.LINE_BRANCH_NO AS LINE_BRANCH_NO
 FROM T_PART_CONS_HISTORY AS T1
 WHERE 1=1
 AND T1.MODEL_CD1 = @IN_MODEL_CD1
 AND T1.MODEL_CD2 = @IN_MODEL_CD2
 AND T1.PRC_KBN <> '3'
 UNION
 SELECT
 T2.BLOCK_NO AS BLOCK_NO,
 T2.LINE_NO AS LINE_NO,
 T2.LINE_BRANCH_NO AS LINE_BRANCH_NO
 FROM M_ORDERTRANS AS T2
 WHERE 1=1
 AND T2.MODEL_CD1 = @IN_MODEL_CD1
 AND T2.MODEL_CD2 = @IN_MODEL_CD2
    ) AS T
   
 SELECT @iCount=MAX(ID) FROM @TMP_TBL

 WHILE @iLoop <= @iCount
 BEGIN
      print '@iLoop:'+convert(nvarchar,@iLoop)

   SELECT
   @CTMP_BLOCK_NO= BLOCK_NO,
   @CTMP_LINE_NO=LINE_NO,
   @CTMP_LINE_BRANCH_NO=LINE_BRANCH_NO
   FROM @TMP_TBL WHERE ID=@iLoop

   SET @iLOOP = @iLOOP + 1

    UPDATE @TMP_TBL
    SET
            DRAW_CD1=@TMP_DRAW_CD1
    ,DRAW_CD2=@TMP_DRAW_CD2
    ,USE_QUANTITY=@TMP_USE_QTY
    ,LEVEL=@TMP_LEVEL
    ,ORDER_FLG=@TMP_ORDER_FLG
    ,ASSY_PLACE=@TMP_ASSY_PLACE
    ,ASSYNO=@TMP_ASSY_NO
    ,FACTORY_KBN=@TMP_FACTORY_KBN
    ,DEL_PLACE=@TMP_DEL_PLACE
    ,SHOW_FLG=1
    WHERE 1=1
    AND BLOCK_NO = @CTMP_BLOCK_NO
    AND LINE_NO = @CTMP_LINE_NO
    AND LINE_BRANCH_NO = @CTMP_LINE_BRANCH_NO

 END
 
    DELETE FROM @TMP_TBL
   
END