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