27,579
社区成员
发帖
与我相关
我的任务
分享
create table usertb(uid int,uname varchar(10),ct int)
insert into usertb select 1,'user',1000 union all select 2,'user2',2000
create table sendtb(id int identity(1,1),uname varchar(10),mobilecode varchar(11),cont nvarchar(1000))
go
create procedure getsendlist
(
@uname varchar(10),@codelist varchar(1000),@msg nvarchar(1000)
)
as
begin
declare @n int
--一条信息的短信数
set @n=len(@msg)/60+sign(len(@msg)%60)
SET NOCOUNT OFF
;with c1 as(
select number,substring(@codelist,number,charindex(',',@codelist+',',number+1)-number)code from master..spt_values
where type='p' and number<=len(@codelist) and substring(@codelist,number,1)<>',' and substring(','+@codelist,number,1)=','
),c2 as(
select row_number()over(order by number)*@n rn,code from c1
)insert into sendtb select @uname,code,@msg from c2 where rn<=(select ct from usertb where uname=@uname)
update usertb set ct=ct-@@rowcount*@n where uname=@uname
end
go
exec getsendlist 'user','135111,136111,137.','我的目的是加入临时发送表后,创建个触发器,首先判断手机号码有多少个,内容有没有超过60个字,超过60个字算多条短信,这样减去客户资料里的短'
select * from sendtb
select * from usertb
/*
id uname mobilecode cont
----------- ---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 user 135111 我的目的是加入临时发送表后,创建个触发器,首先判断手机号码有多少个,内容有没有超过60个字,超过60个字算多条短信,这样减去客户资料里的短
2 user 136111 我的目的是加入临时发送表后,创建个触发器,首先判断手机号码有多少个,内容有没有超过60个字,超过60个字算多条短信,这样减去客户资料里的短
3 user 137. 我的目的是加入临时发送表后,创建个触发器,首先判断手机号码有多少个,内容有没有超过60个字,超过60个字算多条短信,这样减去客户资料里的短
(3 行受影响)
uid uname ct
----------- ---------- -----------
1 user 994
2 user2 2000
(2 行受影响)
*/
go
drop table usertb,sendtb