触发手机短信订阅功能

首先需要买一个短信猫,一般都会提供解决方案

 

USE [WXT_SYS_BASIC]
GO
/****** Object:  StoredProcedure [dbo].[MsgSed]    Script Date: 08/19/2010 17:28:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[MsgSed]
as
declare cur cursor
read_only
for select [uid] from UserMessage 

declare @uid int
open cur

fetch next from cur into @uid
while (@@fetch_status =0)
begin
declare @Phone nvarchar(100)
declare @ToDayAccpetCount nvarchar(100)
declare @ToDateFinCount nvarchar(100)
declare @ToPrice nvarchar(100)
declare @Content nvarchar(Max)
declare @SaleContent nvarchar(Max)
declare @SaleToDayAccpetCount nvarchar(100)
declare @SaleToDayBackCount nvarchar(100)
declare @SalePrice nvarchar(100)
set @Phone=''
set @ToPrice='0'
set @SalePrice='0'
select @Phone=uphone from UserMessage where uid=@uid
select @ToDayAccpetCount=COUNT(*)  from InService where uid=@uid and DATEADD(day,DATEDIFF(day,0,wxDate),0)=DATEADD(day,DATEDIFF(day,0,GETDATE()),0) 
select @ToDateFinCount=COUNT(*) from InService where uid=@uid and DATEADD(day,DATEDIFF(day,0,wxwDate),0)=DATEADD(day,DATEDIFF(day,0,GETDATE()),0) 
select @ToPrice=fabPrice-fabCost  from InService where uid=@uid and DATEADD(day,DATEDIFF(day,0,wxwDate),0)=DATEADD(day,DATEDIFF(day,0,GETDATE()),0) 

select @SaleToDayAccpetCount=COUNT(*) from SalesInfo where uid=@uid and DATEADD(day,DATEDIFF(day,0,salesDate),0)=DATEADD(day,DATEDIFF(day,0,GETDATE()),0) 
select @SaleToDayBackCount=COUNT(*) from SalesInfo where uid=@uid and DATEADD(day,DATEDIFF(day,0,salesBackDate),0)=DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and salesBackType='已退款'
select @SalePrice=salesPrice-salesCost from SalesInfo where uid=@uid and DATEADD(day,DATEDIFF(day,0,salesDate),0)=DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and salesBackType!='已退款'

set @Phone=@Phone+'@sms.xunsai.net'
set @Content='【XXX提醒您：今日受理'+@ToDayAccpetCount+'人，维修完成'+@ToDateFinCount+'人，今日维修毛利'+@ToPrice+'元；请勿回复本短信，欢迎您继续使用XXX】'
set @SaleContent='【XXX提醒您：今日销售'+@SaleToDayAccpetCount+'单，退款'+@SaleToDayBackCount+'单，销售毛利'+@SalePrice+'元，请勿回复本短信，欢迎您继续使用XXX】'

execute msdb.dbo.sp_send_dbmail @profile_name='SMS',
@recipients=@Phone,
@subject='MSG',
@body=@Content
execute msdb.dbo.sp_send_dbmail @profile_name='SMS',
@recipients=@Phone,
@subject='MSG',
@body=@SaleContent
fetch next from cur into @uid
end
close cur
deallocate cur

 

 最后做下计划任务就完成了