sqlserver 临时表应用范例

- 日理万妓 2022-09-30 06:03 20阅读 0赞

\-- =============================================  
\-- 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][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][ID_iLoop]

SET @iLOOP = @iLOOP + 1

UPDATE \#TMP\_M\_PARTSLIST  
          SET  
            [DRAW\_CD1=@TMP\_DRAW\_CD1][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

[iLoop_convert_nvarchar_iLoop]: mailto:'%20rel=
[ID_iLoop]: mailto:ID=@iLoop
[DRAW_CD1_TMP_DRAW_CD1]: mailto:DRAW_CD1=@TMP_DRAW_CD1