[예제로 배우는 MS-SQL] 저장프로시져 예

-- DBMS： Microsoft SQL Server2005
-- 入力パラメータ：
--   @Field～   VARCHAR  --音声データ項目
-- 出力パラメータ：
--   @ReturnCode  INTEGER   リターンコード
--              0 = 成功
--            100 = 業務エラー
--            200 = Updateエラー
--            300 = Insertエラー
--            400 = Deleteエラー
--            500 = 重複エラー
--   @SqlErrorCode INTEGER   SQLエラーコード(@@ERRORの値)
-- ---------------------------------------------------------------------------
-- 作成： 2009/09/16
-- ---------------------------------------------------------------------------
-- 変更： 
-- NOTE： 
--
DROP PROCEDURE CCAPSP_A_ImportAUDIODATA
GO

CREATE PROCEDURE CCAPSP_A_ImportAUDIODATA
 @Field001 VARCHAR(13),
 @Field002 VARCHAR(11),
 @Field003 VARCHAR(3),
 @Field004 VARCHAR(1000),
 @Field005 VARCHAR(10),
 @Field006 VARCHAR(19),
 @Field007 VARCHAR(5),
 @SYSTEM_USER VARCHAR(15),
 @ReturnCode    INTEGER  OUTPUT,           -- 戻り値
 @SqlErrorCode   INTEGER  OUTPUT           -- SQLエラーコード
AS
 -- 共通
 DECLARE @ROWCOUNT   INTEGER       --
 DECLARE @UserUniqueField    VARCHAR(20)                     -- ユーザユニークデータ
    DECLARE @GetDate   DATETIME
   
    -- RECDATA
 DECLARE @RecID    INTEGER
   DECLARE @ID     INTEGER
         
    -- HISTORYDIAL       
 DECLARE @ProfileID          INTEGER
 DECLARE @TalkCount   INTEGER

 -- DIALCTL
    DECLARE @HistoryCode  INTEGER
 DECLARE @SystemPhoneNo1     VARCHAR(15)
   DECLARE @SystemPhoneNo2     VARCHAR(15)
    DECLARE @SystemPhoneNo3     VARCHAR(15)
 --
 -- 出力パラメータ、内部変数を初期化
 --
 
 -- TRIM処理
 -- 全角対象：Field004, Field010
 SELECT
  @Field001 = dbo.ToBothTrim(@Field001, 'half'),
  @Field002 = dbo.ToBothTrim(@Field002, 'half'),
  @Field003 = dbo.ToBothTrim(@Field003, 'half'),
  @Field004 = dbo.ToBothTrim(@Field004, 'full'),
  @Field005 = dbo.ToBothTrim(@Field005, 'half'),
  @Field006 = dbo.ToBothTrim(@Field006, 'half'),
  @Field007 = dbo.ToBothTrim(@Field007, 'half'),
 
 -- 初期化
 SELECT
  @ReturnCode   = 0,
  @SqlErrorCode  = 0,
  @ROWCOUNT      = 0,
  @ClientCode      = 1,             -- 朝日生命(1)固定
  @GetDate         = GETDATE(),
        @JobCode         = 1,
    -- HISTORYDIAL
  @OverlappingFlag = null,
  @ProfileID       = null,
  @TalkCount       = null,
  
  -- DIALCTL   
        @HistoryCode     = null,
        @ImportDate      = LEFT(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-' ,'/'),10)

 -- クライアントデータ取得
 SELECT
        @CustomerCode    = a.CustomerCode
  ,@SystemPhoneNo1 = a.SystemPhoneNo1
  ,@SystemPhoneNo2 = a.SystemPhoneNo2
  ,@SystemPhoneNo3 = a.SystemPhoneNo3 
  ,@PhoneNo        = a.Data048
    ,@CustomerName   = a.Data082
 FROM
  (SELECT
         CustomerCode
   ,SystemPhoneNo1
   ,SystemPhoneNo2
   ,SystemPhoneNo3
      ,Data048
         ,Data082
  FROM
   TB_ClientData
  WHERE
   ClientCode = @ClientCode AND Data064 = @Field001 AND Data065 = @Field002) as a

 -- 顧客データ存在可否チェック
 IF @CustomerCode is not null
  -- 顧客データあり
  BEGIN
       
  --データ重複可否チェック
  SELECT @OverlappingFlag = (SELECT RecID FROM TB_RECDATA WHERE CustomerCode = @CustomerCode and ClientCode = @ClientCode and StartTime = @Field006 and EndTime = @Field008)
  
  IF @OverlappingFlag is not null
   BEGIN
    SELECT
     @ReturnCode = 500
   END

        ELSE

            /******************** データ取込み ********************/
            BEGIN
           
            IF @Field003  is null SELECT @Field003 = ''
         IF @Field004  is null SELECT @Field004 = ''
         IF @Field005  is null SELECT @Field005 = ''
         IF @Field007  is null SELECT @Field007 = ''
         
            /********** TB_RECDATAに追加 **********/
            SELECT @RecID          = (SELECT MAX(RecID) + 1   FROM dbo.TB_RECDATA)
   SELECT @ID              = (SELECT CurrentValue + 1 FROM dbo.TB_SEQUENCE   WHERE Code = (@ClientCode * 100) + 7)
   SELECT @ReceptionResult = (SELECT ResultName       FROM dbo.TB_RESULT     WHERE JobCode = @JobCode AND ResultCode = @Field009)
   SELECT @RecHour         = DATEPART(hh, @Field006)
   SELECT @RecSecond       = DATEDIFF(ss, @Field006, @Field008)
   
   IF @RecID           is null SELECT @RecID           = 1
   IF @ID            is null SELECT @ID              = 1
   IF @RecHour   is null SELECT @RecHour         = ''
      IF @RecSecond  is null SELECT @RecSecond       = ''
      IF @PhoneNo   is null SELECT @PhoneNo         = ''
      IF @CustomerName is null SELECT @CustomerName    = ''
      IF @ReceptionResult is null SELECT @ReceptionResult = @Field009
   
   -- TB_RECDATA
   EXECUTE CCAPSP_A_InsTB_RECDATA
    @RecID     
    ,@ClientCode    --ClientCode
    ,@ID       --ID
    ,@Field006    --StartTime
    ,@Field008    --EndTime
                ,@SYSTEM_USER   --UpdUserID
                ,@GetDate    --Timestamp
                ,@ReturnCode  OUTPUT
    ,@SqlErrorCode  OUTPUT
   
   -- TB_RECDATA Insert失敗処理
   if @ReturnCode != 0 RETURN
   

   /********** TB_HISTORYDIALに追加 **********/
   SELECT @HistoryCode  = (SELECT CurrentValue + 1 FROM dbo.TB_SEQUENCE WHERE Code = (@ClientCode * 100) + 3)
   SELECT @PriDate      = LEFT(@Field006, 10)
   SELECT @PriTime      = RIGHT(@Field008, 8)
   SELECT @LastTalkDate = LEFT(@Field006, 10)
   SELECT @LastTalkTime = RIGHT(@Field008, 8)

            IF @HistoryCode   is null SELECT @HistoryCode = 1
         IF @PriDate       is null SELECT @PriDate       = ''
         IF @PriTime       is null SELECT @PriTime       = ''
         IF @LastTalkDate  is null SELECT @LastTalkDate  = ''
         IF @LastTalkTime  is null SELECT @LastTalkTime  = ''

   -- TB_HISTORYDIAL
   EXECUTE CCAPSP_A_InsTB_HISTORYDIAL
    @ClientCode    --ClientCode
    ,@HistoryCode   --HistoryCode
    ,@CustomerCode   --CustomerCode
    ,@SYSTEM_USER   --UpdUserID
    ,@GetDate     --Timestamp
    ,@ReturnCode  OUTPUT
    ,@SqlErrorCode  OUTPUT
    
   -- TB_HISTORYDIAL Insert失敗処理
   if @ReturnCode != 0 RETURN
   
   /********** TB_DIALCTLに追加 **********/
   -- TB_DIALCTLにデータ存在可否チェック
   -- あれば更新、なければ追加
   
   SET @ProfileID = (SELECT ProfileID FROM dbo.TB_DIALCTL WHERE ClientCode = @ClientCode AND CustomerCode = @CustomerCode)
   
   IF @SystemPhoneNo1 is null SELECT @SystemPhoneNo1 = ''
   IF @SystemPhoneNo2 is null SELECT @SystemPhoneNo2 = ''
   IF @SystemPhoneNo3 is null SELECT @SystemPhoneNo3 = ''
   
   IF @ProfileID is not null
    -- データあり(更新対象)
    BEGIN
    
    SET @TalkCount = (SELECT TalkCount FROM dbo.TB_DIALCTL WHERE ClientCode = @ClientCode AND CustomerCode = @CustomerCode)
                 
    -- 最新データチェック(通話回数が大きいのもが最新データ)
    IF @TalkCount < @Field003
     BEGIN
     
     -- 更新
     EXECUTE CCAPSP_A_UpdTB_DIALCTL
      @ProfileID     --ProfileID
      ,@ClientCode    --ClientCode
      ,@CustomerCode    --CustomerCode
      ,@ImportDate    --ImportDate
      ,@SYSTEM_USER    --UpdUserID
      ,@GetDate      --Timestamp
      ,@ReturnCode  OUTPUT
      ,@SqlErrorCode  OUTPUT
     END
    END
   ELSE

    -- データなし(追加)
    BEGIN
     
     SET @ProfileID = (SELECT MAX(ProfileID) + 1 FROM dbo.TB_DIALCTL)
     IF @ProfileID is null SET @ProfileID = 1
     
     EXECUTE CCAPSP_A_InsTB_DIALCTL
      @ProfileID     --ProfileID
      ,@ClientCode    --ClientCode
      ,@CustomerCode    --CustomerCode
      ,@ImportDate    --ImportDate
      ,@SYSTEM_USER    --UpdUserID
      ,@GetDate      --Timestamp
      ,@ReturnCode  OUTPUT
      ,@SqlErrorCode  OUTPUT
    END

    -- TB_DIALCTL Insert/Update失敗処理
    if @ReturnCode != 0 RETURN

   END
        END
 ELSE
  -- 顧客データなし
  /******************** エラー処理 ********************/
  BEGIN
   SELECT @ReturnCode   = 100
  END
 
 RETURN
GO

GRANT EXECUTE ON CCAPSP_A_ImportAUDIODATA TO af_user
GO