聽說幸福…在山的那邊 (浪機子)

USE [TempDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetMailBody]    Script Date: 2018/1/30 上午 10:13:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--================================================================================
--撰寫日期 :
--撰寫人員 :
--程式說明 :
--使用範例 :
--規　　格 :
--異動記錄 : 將mail的body內容值[XXX]更換成正確的資料
-- 20170920 add   主旨
--EXEC usp_GetMailBody 'TEST','CFPMain','name','107006'
--================================================================================
ALTER PROCEDURE [dbo].[usp_GetMailBody]
( 
     @CallProg VARCHAR(20),
  @CaseNO VARCHAR(48),       --案件編號 
  @MailToName varchar(max),   --mail
  @MailBody VARCHAR(max),    --mail
  @EmpID VARCHAR(10)         --處理人員
)
AS
BEGIN
SET NOCOUNT OFF;

DECLARE @ERROR int
DECLARE @ROWCOUNT int
DECLARE @ERROR_MESSAGE nvarchar(4000)
DECLARE @SQL varchar(max)
DECLARE @Priority varchar(50)
DECLARE @Type varchar(50)
DECLARE @SystemDat varchar(19)
DECLARE @Subject nvarchar(200)

SET @SQL = ''
 
SET @SQL = 'exec  usp_GetMailBody ' + @CallProg + ',' + @CaseNO + ',' + @MailToName + ',' + @MailBody  + ',' + @EmpID

INSERT  Sys_Log   ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
VALUES (GETDATE(),@CaseNO,'usp_GetMailBody',@CallProg,'Get MailBody','Start','','','','',@SQL,@EmpID)
  
BEGIN
    BEGIN TRY
  SELECT @Priority = ISNULL(Para.ParaText,'') ,@Type=ISNULL(Type.TypeName,''),@SystemDat=Convert(char(19),getdate(),120)
  ,@Subject=Subject
  FROM Tbl_caseMain
        LEFT JOIN Sys_Parameter Para on Para.ParaType='Public' and  Para.ParaKind  = 'Priority' and Tbl_CaseMain.Priority =  Para.ParaValue
  LEFT JOIN Sys_Type Type on Tbl_CaseMain.Type =  Type.TypeID
        WHERE CaseNo =@CaseNo
  
  --案件編號
   SELECT @MailBody = REPLACE(@MailBody, '[Title]', @MailToName)
  
  --案件優先順序
   SELECT @MailBody = REPLACE(@MailBody, '[Priority]', @Priority)

  --轉介類別
   SELECT @MailBody = REPLACE(@MailBody, '[Type]', @Type)

  --案件編號
   SELECT @MailBody = REPLACE(@MailBody, '[CaseNo]', @CaseNO)

   --主旨
   SELECT @MailBody = REPLACE(@MailBody, '[Subject]', @Subject)

   --發送的系統日
   SELECT @MailBody = REPLACE(@MailBody, '[SystemDat]', @SystemDat)

      GOTO END_DATA
 END TRY
 BEGIN CATCH
  SET @ERROR = @@ERROR
  SET @ROWCOUNT = @@ROWCOUNT
  SET @ERROR_MESSAGE = ERROR_MESSAGE()
 END CATCH

END 
-----------------------------------------------------------
END_DATA:
SET @ERROR = 0
SET @ERROR_MESSAGE = '有資料'
print 'usp_GetMailBody 有資料'
GOTO END_PROG
---------------------------------------------------------- 
END_PROG:
 IF @ERROR <> 0
 BEGIN
  INSERT  Sys_Log   ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
  VALUES (GETDATE(),@CaseNO,'usp_GetMailBody',@CallProg,'Get MailBody','Error','','','',@ERROR_MESSAGE,@MailBody,@EmpID)
    
  print 'usp_GetMailBody 程式發生錯誤：' +  @ERROR_MESSAGE

     RAISERROR(@ERROR_MESSAGE , 18, 11)
 END
 ELSE
 BEGIN   
     INSERT  Sys_Log   ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
  VALUES (GETDATE(),@CaseNO,'usp_GetMailBody',@CallProg,'Get MailBody','End','','','',@ERROR_MESSAGE,@MailBody,@EmpID)
  
  print 'usp_GetMailBody 執行完成：' +   convert(char(23),getdate(),21)
       
  SELECT   @MailBody

 END 
END