短信猫存储过程

-------------------------------------------------短信猫存储过程---------------------------------------------------------------------------
–定义存储过程，在程序中调用 call/exec ?
create PROC [dbo].[SEND_SMS](
@remark varchar(300)

)
AS
BEGIN
DECLARE @IS_SEND VARCHAR(1)
DECLARE @SEND_TIME VARCHAR(20)
declare @deviceGuid varchar(36)
declare @custId varchar(36)
declare @custName varchar(50)
declare @tel varchar(20)
declare @warnMoney varchar(20)
declare @mayAcc varchar(20)
declare @key VARCHAR(1)
declare @factoryNo varchar(12)
declare @userpk varchar(36)
declare @tel2 varchar(20)
declare @tel3 varchar(20)
declare @telAll varchar(50)
–获取是否发送短信
SELECT @key=SIGN,@SEND_TIME=PARAM_VALUE FROM SYS_PARAM WHERE PARAM_ID=‘107’ --是否开启发短信
SELECT @IS_SEND=SIGN,@SEND_TIME=PARAM_VALUE FROM SYS_PARAM WHERE PARAM_ID=‘104’–发短信时间
select @userpk=userrealname from sys_user where username=‘admin’
IF @IS_SEND=1
BEGIN
IF ISNUMERIC(@SEND_TIME)=1 --是数字的话加入数据库
BEGIN
IF LEN(@SEND_TIME)=1
BEGIN
SET @SEND_TIME=‘0’+@SEND_TIME;
END
declare @ex int
set @ex=0
IF @key=1
begin
–查找需要发送的数据信息
DECLARE METER CURSOR FOR
select cust_guid,cust_id,cust_name,tel,tel2,tel3,warn_money,may_acc from cust_info where
may_acc<warn_money and isnull(sms,0) <(select param_value from sys_param where param_id=‘118’ )
OPEN METER
–fetch into把当前游标取得值赋值给变量：@deviceGuid,@custId,@custName,@tel,@tel2,@tel3,@warnMoney,@mayAcc与插入类似，一一对应顺序不能错
FETCH NEXT FROM METER INTO @deviceGuid,@custId,@custName,@tel,@tel2,@tel3,@warnMoney,@mayAcc
–0的时候，游标查询的结果有，游标就会将查询到的数据直接存入游标捕捉数据的变量中,0表示fetch语句成功。
WHILE @@FETCH_STATUS = 0
BEGIN
–插入系统得发送记录
set @ex=@ex+1
set @telAll=’’;
IF LEN(@tel)=11
BEGIN
INSERT INTO smsdb.DBO.OutBox
–Replace（“字符串”,“要被替代的字符串”,“替代后的字符串”）
SELECT ‘’,@tel,replace(replace(@remark,’@2’,CONVERT(VARCHAR(50),@custName)),’@5’,CONVERT(VARCHAR(20),@mayAcc)),
dateAdd(second,@ex30,CONVERT(VARCHAR(10),GETDATE(),120)+’ ‘+CONVERT(VARCHAR(2),@SEND_TIME)+’:01:01’),0,0,’’,’’,’’,’’,’’
set @telAll=@tel+’,’;
end
IF LEN(@tel2)=11
BEGIN
INSERT INTO smsdb.DBO.OutBox
–Replace（“字符串”,“要被替代的字符串”,“替代后的字符串”）
SELECT ‘’,@tel2,replace(replace(@remark,’@2’,CONVERT(VARCHAR(50),@custName)),’@5’,CONVERT(VARCHAR(20),@mayAcc)),
dateAdd(second,@ex30,CONVERT(VARCHAR(10),GETDATE(),120)+’ ‘+CONVERT(VARCHAR(2),@SEND_TIME)+’:01:01’),0,0,’’,’’,’’,’’,’’
set @telAll=@telAll+’,’+@tel2
end
IF LEN(@tel3)=11
BEGIN
INSERT INTO smsdb.DBO.OutBox
–Replace（“字符串”,“要被替代的字符串”,“替代后的字符串”）
SELECT ‘’,@tel3,replace(replace(@remark,’@2’,CONVERT(VARCHAR(50),@custName)),’@5’,CONVERT(VARCHAR(20),@mayAcc)),
dateAdd(second,@ex*30,CONVERT(VARCHAR(10),GETDATE(),120)+’ ‘+CONVERT(VARCHAR(2),@SEND_TIME)+’:01:01’),0,0,’’,’’,’’,’’,’’
set @telAll=@telAll+’,’+@tel3;
end
if len(@telAll)>=11
begin
–通过这句话把信息存储到SENDMESSAGE这张数据库表中的
INSERT INTO SENDMESSAGE
SELECT NEWID(),’’,@telAll,‘9’,CONVERT(VARCHAR(10),GETDATE(),120)+’ ‘+CONVERT(VARCHAR(2),@SEND_TIME)+’:01:01’,replace(replace(@remark,’@2’,CONVERT(VARCHAR(50),@custName)),’@5’,CONVERT(VARCHAR(20),@mayAcc)),@userpk,0,@deviceGuid,@custName
–更新次数
update cust_info set sms=cast((cast(isnull(sms,‘0’) as int)+1) as varchar(2)) where cust_guid=@deviceGuid
end
–fetch into把当前游标取得值赋值给变量
FETCH NEXT FROM METER INTO @deviceGuid,@custId,@custName,@tel,@tel2,@tel3,@warnMoney,@mayAcc
END
CLOSE METER
–DEALLOCATE 释放语句
DEALLOCATE METER
END
end
END
END