StoreProcedure

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