短消息发送的SQL过程

来源：互联网发布：百度指数大数据编辑：程序博客网时间：2024/04/29 21:51

ALTER PROCEDURE [dbo].[SP_SMS_SetMsgReceiver]
@MsgID      int,
@Receivers           varchar(5000),
@MobileNo     varchar(5000),
@Type      int

/*

============================================================
功能: 短消息发送
参数:
@MsgID                  int   : 消息ID
@Receivers      varchar(5000), : 接收者字符串，用逗号相隔
@MobileNo      varchar(5000), : 手机号码，用逗号相隔
@Type                   int   ; 1站内用户 2站外用户
注意:    @Receivers与@MobileNo必须一一对应
============================================================

AS
SET NOCOUNT ON
DECLARE @Receiver varchar(300)
DECLARE @Mobile varchar(300)

WHILE LEN(@Receivers)>0
BEGIN
--如果不是最后一次
IF(charindex(',',@Receivers)>0)
BEGIN
--截取逗号前数据
SET @Receiver = substring(@Receivers,1,charindex(',',@Receivers)-1)
--判断手机号码是否为空
IF(LEN(@MobileNo)>0)
  SET @Mobile     = substring(@MobileNo,1,charindex(',',@MobileNo)-1)
ELSE
  SET @Mobile=''
--剪切字符串
SET @Receivers = substring(@Receivers,charindex(',',@Receivers)+1,LEN(@Receivers))
--判断手机号码是否为空
IF(LEN(@MobileNo)>0)
  SET @MobileNo = substring(@MobileNo,charindex(',',@MobileNo)+1,LEN(@MobileNo))
ELSE
  SET @MobileNo=''
INSERT INTO
     UDS_SMS_Receiver
   VALUES
   (@MsgID,@Receiver,@Mobile,@Type,0)
--print @Receiver
--print @Mobile
END
ELSE
BEGIN
  --PRINT @Receivers
  --PRINT @MobileNo
INSERT INTO
   UDS_SMS_Receiver
   VALUES
   (@MsgID,@Receivers,@MobileNo,@Type,0)
  BREAK
END

END

SET NOCOUNT OFF