sqlserver 变量表应用 范例

概述-- ============================================= -- Description: <> -- Author:  <> -- Create date: <> -- ============================================= CREATE PROCEDURE [dbo].[pro_get_parts_list]  @IN_

-- =============================================
-- 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
 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

总结

以上是内存溢出为你收集整理的sqlserver 变量表应用 范例全部内容，希望文章能够帮你解决sqlserver 变量表应用 范例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。