钉钉消息格式

CREATE PROCEDURE sp_dingmessage_text(@touser NVARCHAR(2000)='',@toparty NVARCHAR(2000)='',@agentid NVARCHAR(20), @content NVARCHAR(2000), @message NVARCHAR(4000) OUTPUT)
AS
BEGIN 
 IF ISNULL(@touser,'')='' AND ISNULL(@toparty,'')=''
 BEGIN
  RAISERROR ('SQL:发送人或部门不能同时为空!', 16 ,1 ) ;   
  RETURN -1 
 END
 
 IF ISNULL(@content,'')=''
 BEGIN
  RAISERROR ('SQL:消息内容不允许为空!', 16 ,1 ) ;   
  RETURN -1 
 END  
  
 IF NOT EXISTS(SELECT * FROM dbo.y_platform_agent WHERE agent_id=@agentid)
 BEGIN
  RAISERROR ('SQL:Agent_id不存在,请检查!', 16 ,1 ) ;   
  RETURN -1 
 END 
  
 SET @message=N'{
 "touser":"'+ISNULL(@touser,'')+'",
 "toparty":"'+ISNULL(@toparty,'')+'",
 "agentid":"'+ISNULL(@agentid,'')+'",
 "msgtype": "text",
 "text": {
  "content": "'+ISNULL(@content,'')+'"}
 }'
END

 

CREATE PROCEDURE sp_dingmessage_link(@touser NVARCHAR(2000)='',@toparty NVARCHAR(2000)='',@agentid NVARCHAR(20),@messageUrl VARCHAR(200),@picUrl VARCHAR(200),@title NVARCHAR(100),@content NVARCHAR(2000), @message NVARCHAR(4000) OUTPUT)
AS
BEGIN 
 IF ISNULL(@touser,'')='' AND ISNULL(@toparty,'')=''
 BEGIN
  RAISERROR ('SQL:发送人或部门不能同时为空!', 16 ,1 ) ;   
  RETURN -1 
 END
 
 IF ISNULL(@content,'')=''
 BEGIN
  RAISERROR ('SQL:消息内容不允许为空!', 16 ,1 ) ;   
  RETURN -1 
 END  
  
 IF NOT EXISTS(SELECT * FROM dbo.y_platform_agent WHERE agent_id=@agentid)
 BEGIN
  RAISERROR ('SQL:Agent_id不存在,请检查!', 16 ,1 ) ;   
  RETURN -1 
 END 
  
 SET @message=N'{
 "touser":"'+ISNULL(@touser,'')+'",
 "toparty":"'+ISNULL(@toparty,'')+'",
 "agentid":"'+ISNULL(@agentid,'')+'",
 "msgtype": "link",
 "link": {
        "messageUrl": "'+ISNULL(@messageUrl,'')+'",
        "picUrl":"'+ISNULL(@picUrl,'')+'",
        "title": "'+ISNULL(@title,'')+'",
        "text": "'+ISNULL(@content,'')+'"}
 }'
END


CREATE PROCEDURE sp_dingmessage_oa(@touser NVARCHAR(2000)='',@toparty NVARCHAR(2000)='',@agentid NVARCHAR(20),@messageUrl varchar(100)='',@headtitle NVARCHAR(100),@bodytitle NVARCHAR(100),@picUrl VARCHAR(200)='',@content NVARCHAR(2000),@author nvarchar(50)='', @message NVARCHAR(4000) OUTPUT)
AS
BEGIN 
 IF ISNULL(@touser,'')='' AND ISNULL(@toparty,'')=''
 BEGIN
  RAISERROR ('SQL:发送人或部门不能同时为空!', 16 ,1 ) ;   
  RETURN -1 
 END
 
 IF ISNULL(@content,'')=''
 BEGIN
  RAISERROR ('SQL:消息内容不允许为空!', 16 ,1 ) ;   
  RETURN -1 
 END  
  
 IF NOT EXISTS(SELECT * FROM dbo.y_platform_agent WHERE agent_id=@agentid)
 BEGIN
  RAISERROR ('SQL:Agent_id不存在,请检查!', 16 ,1 ) ;   
  RETURN -1 
 END 
  
 SET @message=N'{
 "touser":"'+ISNULL(@touser,'')+'",
 "toparty":"'+ISNULL(@toparty,'')+'",
 "agentid":"'+ISNULL(@agentid,'')+'",
 "msgtype": "oa",
  "oa": {
        "message_url": "'+ISNULL(@messageUrl,'')+'",
        "head": {
            "bgcolor": "FFBBBBBB",
            "text": "'+ISNULL(@headtitle,'')+'"
        },
        "body": {
            "title": "'+ISNULL(@bodytitle,'')+'",
            "content": "'+ISNULL(@content,'')+'",
            "image": "'+ISNULL(@picUrl,'')+'",
            "author": "'+ISNULL(@author,'')+'"
        }
    }
}'
END