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
 
 IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#TMP_M_PARTSLIST')
 DROP TABLE #TMP_M_PARTSLIST
 
 CREATE TABLE #TMP_M_PARTSLIST (
        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
       ,CONSTRAINT pk_tmp_m_partslist PRIMARY KEY (
          BLOCK_NO
          ,LINE_NO
          ,LINE_BRANCH_NO
          )
                           )

 INSERT INTO #TMP_M_PARTSLIST WITH(TABLOCK)
 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_M_PARTSLIST

 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_M_PARTSLIST WHERE ID=@iLoop

   SET @iLOOP = @iLOOP + 1

          UPDATE #TMP_M_PARTSLIST
          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

    TRUNCATE TABLE #TMP_M_PARTSLIST
 IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#TMP_M_PARTSLIST')
 DROP TABLE #TMP_M_PARTSLIST
END