sqlserver中存储过程和游标的使用
DECLARE
@phone nvarchar(20)
BEGIN
declare @count int=0
declare @dateNow nvarchar(100) =convert(nvarchar, getdate(),120)
declare @dateNowFormat nvarchar(20) =convert(nvarchar(100), getdate(), 23) print @dateNowFormat
declare @datepre nvarchar(100)=convert(nvarchar(100), CONVERT(varchar(100), dateadd(DAY,-1,@dateNowFormat), 23), 23)
declare @tableBack nvarchar(100)=‘sms_userDeadPhone_‘+@dateNowFormat
print @dateNow
print @datepre
-- 定义游标.
DECLARE DeadPhone CURSOR FAST_FORWARD FOR
select UserPhone from SMS_UserDB.dbo.SMS_YDReciveMessage where (UserMessage like ‘%TD%‘ or UserMessage like ‘%td%‘) and MessageDate between @datepre and @dateNow
-- 打开游标.
OPEN DeadPhone;
--填充数据.
FETCH NEXT FROM DeadPhone INTO @phone;
--假如检索到了数据，才处理.
WHILE @@fetch_status = 0
BEGIN
print @phone;
--查询号码是否已经存在
select @count=COUNT(1) from SMS_UserDeadPhone where UserPhone=@phone
if(@count<=0)
begin
--select * into UserDeadPhone from SMS_UserDeadPhone
insert into DeadPhonetable(UserPhone,UserReson,CreateDate)values(@phone,‘屏蔽‘,GETDATE())
end
--填充下一条数据.
FETCH NEXT FROM DeadPhone INTO @phone;
END
-- 关闭游标
CLOSE DeadPhone;
--释放游标.