1. Create SP
Create PROCEDURE [dbo].[SP_Test001]
@IDFRM VARCHAR(10) ,
@TABLENAME VARCHAR(40),
@COLUNMNAME VARCHAR(40),
@CVALUE VARCHAR(100),
@CURIDDOC INT
AS
DECLARE @SQL AS VARCHAR (MAX)
SET @SQL =
' SELECT * FROM B WHERE B.DTLASTACPT = CONVERT(VARCHAR(10),GETDATE(),111)'
EXEC(@SQL)
strSQL = " EXEC SP_Test "
strSQL &= " @IDDOC= " & Me.mIDDoc
clsDB = New Database
clsDB.ConnectionString = "......."
clsDB.Connect()
dts = New DataSet
clsDB.CommandExecute(strSQL, "SP_TEST", dts)
ALTER PROCEDURE [dbo].[SP_0013]
(
@IDDOC int
,@NEWUSERID nvarchar(20)
,@NENDO nvarchar(4)
,@TERM nvarchar(10)
)
AS
DECLARE @IDDOC int
DECLARE @IDSEQ int
DECLARE @TARGET nvarchar(MAX)
DECLARE @WEIGHT1 int
DECLARE @DIFFCULTY1 nvarchar(3)
DECLARE @WEIGHT2 int
DECLARE @DIFFCULTY2 nvarchar(3)
DECLARE @DEFAULT_TERMKUBUN_KIMATU nvarchar(10)
IF EXISTS(SELECT VLSTATUS FROM EGGA0001 WHERE IDDOC=@KIMATU_IDDOC AND VLSTATUS=50)
BEGIN
RETURN
END
SET @DEFAULT_TERMKUBUN_KIMATU = '1'
SET @IDDOC =(
SELECT TOP 1
ISNULL(A.IDDOC,0)
FROM MBOA0004 A
WHERE
A.NEWUSERID = @NEWUSERID
AND A.NENDO = @NENDO
AND A.TERM = @TERM
ORDER BY A.IDDOC DESC)
SET @iddoc= ISNULL(@iddoc,0)
IF @IDDOC =0
BEGIN
Return
END
BEGIN TRAN
IF EXISTS(Select * from MBOG0003 WHERE IDDOC = @KIMATU_IDDOC)
BEGIN
UPDATE MBOG0003 SET
IDDOCBEF=@IDDOC,
NEWUSERID=@NEWUSERID,
NENDO=@NENDO,
TERM=@TERM,
TERMKUBUN=@DEFAULT_TERMKUBUN_KIMATU,
DTUPDATE=GETDATE()
WHERE
IDDOC=@KIMATU_IDDOC
END
ELSE
BEGIN
INSERT
MBOG0003
(
IDDOC,
IDDOCBEF,
NEWUSERID,
NENDO,
TERM,
TERMKUBUN,
DTUPDATE
)
VALUES
(
@KIMATU_IDDOC,
@IDDOC,
@NEWUSERID,
@NENDO,
@TERM,
@DEFAULT_TERMKUBUN_KIMATU,
GETDATE()
)
END
UPDATE MBOG0004 SET
TARGET = '',
WEIGHT1 = 0,
DIFFCULTY1 = '',
WEIGHT2 = 0,
DIFFCULTY2 = ''
WHERE IDDOC = @KIMATU_IDDOC
DECLARE CUR_MOKUHYOU CURSOR
FOR
SELECT
IDSEQ,
TARGET,
WEIGHT1,
DIFFCULTY1,
WEIGHT2,
DIFFCULTY2
FROM
MBOG0001
WHERE
IDDOC = @IDDOC
OPEN CUR_MOKUHYOU
FETCH NEXT FROM CUR_MOKUHYOU INTO @IDSEQ, @TARGET, @WEIGHT1, @DIFFCULTY1, @WEIGHT2, @DIFFCULTY2
WHILE(@@fetch_status=0)
BEGIN
IF EXISTS(SELECT * FROM MBOG0004 WHERE IDDOC = @KIMATU_IDDOC AND IDSEQ = @IDSEQ)
BEGIN
UPDATE MBOG0004 SET
NEWUSERID = @NEWUSERID,
NENDO = @NENDO,
TERM = @TERM,
TERMKUBUN = @DEFAULT_TERMKUBUN_KIMATU,
TARGET = @TARGET,
WEIGHT1 = @WEIGHT1,
DIFFCULTY1 = @DIFFCULTY1,
WEIGHT2 = @WEIGHT2,
DIFFCULTY2 = @DIFFCULTY2,
DTUPDATE=GETDATE()
WHERE IDDOC = @KIMATU_IDDOC AND IDSEQ = @IDSEQ
END
ELSE
BEGIN
INSERT
MBOG0004
(
IDDOC,
IDSEQ,
NEWUSERID,
NENDO,
TERM,
TERMKUBUN,
TARGET,
WEIGHT1,
DIFFCULTY1,
WEIGHT2,
DIFFCULTY2,
DTUPDATE
)
VALUES
(
@KIMATU_IDDOC,
@IDSEQ,
@NEWUSERID,
@NENDO,
@TERM,
@DEFAULT_TERMKUBUN_KIMATU,
@TARGET,
@WEIGHT1,
@DIFFCULTY1,
@WEIGHT2,
@DIFFCULTY2,
GETDATE()
)
END
FETCH NEXT FROM CUR_MOKUHYOU INTO @IDSEQ, @TARGET, @WEIGHT1, @DIFFCULTY1, @WEIGHT2, @DIFFCULTY2
END
CLOSE CUR_MOKUHYOU
DEALLOCATE CUR_MOKUHYOU
IF @@ERROR=0
BEGIN
commit
END
else
BEGIN
rollback
END