SQL SERVER 单游标存储过程

SQL SERVER 单游标存储过程模板

USE [PWMS_YANTAI]
GO
 Object  StoredProcedure [dbo].[PWMS_CW_PDA_PICKINGOUT]    Script Date 2017-06-23 94225 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------------------
  1.SYSTEM        동양기전 WMS(창원)
  2.SUBSYSTEM     PDA
  3.PROGRAM ID   DY_WMS_CWPDA.W_PP1U22
  6.DESCRIPTION  PDA 가용재고 이동처리
  7.Author        ZHANG
  8.HISTORY
  ----------------------------------------------------------------------------
                        변     경    사    항
  ----------------------------------------------------------------------------
     IO         NAME        VERSION       DESCRIPTION
  ----------  ------------  ----------  --------------------------------------
  2017-06-23    zhang       1.0       Initialization
  exec [dbo].[PWMS_CW_PDA_PICKINGOUT] 
  ----------------------------------------------------------------------------
 
 CREATE PROCEDURE [dbo].[PWMS_CW_PDA_PICKINGOUT]
    @S_BUKRS    NCHAR(4),
    @S_WERKS    NCHAR(4),
    @S_STOCCD   varchar(4),
    @sPrc       varchar(4),
    --@S_OUTSEQ    NVARCHAR(10),
    --@S_OUTQTY DECIMAL(13,3),
    --@S_OUTDAY    NVARCHAR(20),
    @S_KOMAU NVARCHAR(15),
    @S_USERID   NVARCHAR(20)

    AS

SET NOCOUNT ON
DECLARE
        @V_MSG       VARCHAR(500),
        @V_RTN       VARCHAR(300)   

BEGIN TRAN

BEGIN TRY

    DECLARE @S_OUTSEQ    NVARCHAR(10)
    DECLARE @S_OUTQTY   DECIMAL(13,3)
    DECLARE @S_OUTDAY    NVARCHAR(20)

    DECLARE cur_MOVE  CURSOR FOR

        SELECT  A.BARCD,A.qty,A.JOBDAY          
          FROM CW_PDAQUE A
         WHERE A.PLANT = @S_WERKS
           AND A.BUKRS = @S_BUKRS
           AND A.STL = @s_STOCCD
           AND A.PRC = @sPrc
           AND A.USERID = @s_USERID
           AND A.QTY0
           AND A.STATE ='X'
           AND A.ORDNO = @S_KOMAU
          ORDER BY A.SEQ;

        OPEN cur_MOVE

        FETCH NEXT FROM cur_MOVE INTO
            @S_OUTSEQ,@S_OUTQTY,@S_OUTDAY

            WHILE (@@FETCH_STATUS = 0)
                BEGIN       
                    
                    




    FETCH NEXT FROM cur_MOVE INTO
                                  @sSeq, @sMatcod,@dQty,@sLocidr,@sLocidh,@sLocidd,@IDSEQ
                    END
                    CLOSE cur_MOVE
                    DEALLOCATE cur_MOVE

END TRY
BEGIN CATCH
    SELECT @V_MSG = SUBSTRING(ERROR_MESSAGE(),1,500)
    GOTO ERROR_TRANSACTION
END CATCH


SET NOCOUNT OFF

 ---- 트랜잭션 성공
    SELECT 'OK' ProcFlag, LEFT(@V_RTN, LEN(@V_RTN)-1), @V_MSG

    --적재 Que 완료처리
    UPDATE CW_PDAQUE
       SET STATE='Y'
     WHERE PDAID = @sPdaid;
    
    COMMIT TRAN

    PRINT  '[WMS_CW_PDA_MOVEGOOD] COMMIT 완료'    
    
    GOTO END_BATCH

    ---- 트랜잭션 구문 안에서 실패
ERROR_TRANSACTION

    SELECT 'NG' ProcFlag, @V_MSG
    ROLLBACK TRAN

    PRINT  '[WMS_CW_PDA_MOVEGOOD] ROLLBACK 완료'  

    BEGIN TRAN
        --적재 Que 완료처리
        UPDATE CW_PDAQUE
           SET dsc= '@V_MSG ' + @V_MSG,
               STATE='E'
         WHERE PDAID = @sPdaid
           AND SEQ=@sSeq;

    COMMIT TRAN
    
    BEGIN TRAN
    ROLLBACK TRAN
    
    GOTO END_BATCH

    ---- 트랜잭션 구문 밖에서 실패
ERROR_ACTION

    SELECT 'NG' ProcFlag, @V_MSG

    BEGIN TRAN
        --적재 Que 완료처리
        UPDATE CW_PDAQUE
           SET dsc= @V_MSG,
               STATE='E'    
         WHERE PDAID = @sPdaid;

    COMMIT TRAN

END_BATCH       
···