sqlserver 变量表应用范例

不念不忘少年蓝@ 2022-09-30 06:04 175阅读 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  
   
 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][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][ID_iLoop]

SET @iLOOP = @iLOOP + 1

UPDATE @TMP\_TBL  
    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  
   
    DELETE FROM @TMP\_TBL  
     
END

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