27,582
社区成员




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