- 浏览: 1012874 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
jingxuewang2010:
gvParameter是什么?
GridView绑定xml,增,删,改,取消 -
学不止:
...
FreeSwitch Sip -
lushcenter:
个人学习中!
Ajax 服务器推送 -
amcucn:
楼主可以自学啊,如果你想做一个软件,从无到有。那你可以选择一门 ...
计算机专业前途几何 ?觉得很迷茫 -
Technoboy:
终于看完了
字符集和字符编码(Charset & Encoding)
联通短信网关(SGIP 1.2) 数据库设计脚本
-- 表生成
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sgip_Deliver]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[sgip_Deliver]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sgip_Report]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sgip_Report]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sgip_bill]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sgip_bill]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sgip_submit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sgip_submit]
GO
CREATE TABLE [dbo].[sgip_Deliver] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[phone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[spNumber] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[MessageCoding] [int] NULL ,
[MessageLength] [int] NULL ,
[MessageContent] [varchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
[linkID] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[add_time] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sgip_Report] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[SequenceNumber] [int] NULL ,
[SequenceNumber1] [int] NULL ,
[SequenceNumber2] [int] NULL ,
[ReportType] [int] NULL ,
[phone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[State] [int] NULL ,
[ErrorCode] [int] NULL ,
[Reserve] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[add_time] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sgip_bill] (
[id] [int] NOT NULL ,
[spNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ChargeNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[UserNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[UserCount] [int] NULL ,
[CorpId] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[ServiceType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeType] [int] NULL ,
[FeeValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[GivenValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[AgentFlag] [int] NULL ,
[MorelatetoMTFlag] [int] NULL ,
[Priority] [int] NULL ,
[ExpireTime] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[ScheduleTime] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[ReportFlag] [int] NULL ,
[TP_pid] [int] NULL ,
[TP_udhi] [int] NULL ,
[MessageCoding] [int] NULL ,
[MessageType] [int] NULL ,
[MessageContent] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[linkID] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[smsFlag] [int] NULL ,
[sendCount] [int] NULL ,
[lastSendTime] [datetime] NULL ,
[submit_add_time] [datetime] NULL ,
[result] [int] NULL ,
[add_time] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sgip_submit] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[spNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ChargeNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[UserNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[UserCount] [int] NULL ,
[CorpId] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[ServiceType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FeeType] [int] NULL ,
[FeeValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[GivenValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[AgentFlag] [int] NULL ,
[MorelatetoMTFlag] [int] NULL ,
[Priority] [int] NULL ,
[ExpireTime] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[ScheduleTime] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[ReportFlag] [int] NULL ,
[TP_pid] [int] NULL ,
[TP_udhi] [int] NULL ,
[MessageCoding] [int] NULL ,
[MessageType] [int] NULL ,
[MessageContent] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[linkID] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[smsFlag] [int] NULL ,
[sendCount] [int] NULL ,
[lastSendTime] [datetime] NULL ,
[add_time] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sgip_Deliver] ADD
CONSTRAINT [DF_sgip_Deliver_add_time] DEFAULT (getdate()) FOR [add_time],
CONSTRAINT [PK_sgip_Deliver] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sgip_Report] ADD
CONSTRAINT [DF_sgip_Report_add_time] DEFAULT (getdate()) FOR [add_time],
CONSTRAINT [PK_sgip_Report] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sgip_bill] ADD
CONSTRAINT [DF_sgip_bill_UserCount] DEFAULT (1) FOR [UserCount],
CONSTRAINT [DF_sgip_bill_FeeValue] DEFAULT ('000000') FOR [FeeValue],
CONSTRAINT [DF_sgip_bill_GivenValue] DEFAULT ('000000') FOR [GivenValue],
CONSTRAINT [DF_sgip_bill_AgentFlag] DEFAULT (0) FOR [AgentFlag],
CONSTRAINT [DF_sgip_bill_Priority] DEFAULT (0) FOR [Priority],
CONSTRAINT [DF_sgip_bill_ReportFlag] DEFAULT (0) FOR [ReportFlag],
CONSTRAINT [DF_sgip_bill_TP_pid] DEFAULT (0) FOR [TP_pid],
CONSTRAINT [DF_sgip_bill_TP_udhi] DEFAULT (0) FOR [TP_udhi],
CONSTRAINT [DF_sgip_bill_MessageCoding] DEFAULT (0) FOR [MessageCoding],
CONSTRAINT [DF_sgip_bill_MessageType] DEFAULT (0) FOR [MessageType],
CONSTRAINT [DF_sgip_bill_smsFlag] DEFAULT (0) FOR [smsFlag],
CONSTRAINT [DF_sgip_bill_sendCount] DEFAULT (0) FOR [sendCount],
CONSTRAINT [DF_sgip_bill_add_time] DEFAULT (getdate()) FOR [submit_add_time],
CONSTRAINT [DF_sgip_bill_add_time_1] DEFAULT (getdate()) FOR [add_time],
CONSTRAINT [PK_sgip_bill] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sgip_submit] ADD
CONSTRAINT [DF_sgip_submit_UserCount] DEFAULT (1) FOR [UserCount],
CONSTRAINT [DF_sgip_submit_FeeValue] DEFAULT ('000000') FOR [FeeValue],
CONSTRAINT [DF_sgip_submit_GivenValue] DEFAULT ('000000') FOR [GivenValue],
CONSTRAINT [DF_sgip_submit_AgentFlag] DEFAULT (0) FOR [AgentFlag],
CONSTRAINT [DF_sgip_submit_Priority] DEFAULT (0) FOR [Priority],
CONSTRAINT [DF_sgip_submit_ReportFlag] DEFAULT (0) FOR [ReportFlag],
CONSTRAINT [DF_sgip_submit_TP_pid] DEFAULT (0) FOR [TP_pid],
CONSTRAINT [DF_sgip_submit_TP_udhi] DEFAULT (0) FOR [TP_udhi],
CONSTRAINT [DF_sgip_submit_MessageCoding] DEFAULT (0) FOR [MessageCoding],
CONSTRAINT [DF_sgip_submit_MessageType] DEFAULT (0) FOR [MessageType],
CONSTRAINT [DF_sgip_submit_smsFlag] DEFAULT (0) FOR [smsFlag],
CONSTRAINT [DF_sgip_submit_sendCount] DEFAULT (0) FOR [sendCount],
CONSTRAINT [DF_sgip_submit_add_time] DEFAULT (getdate()) FOR [add_time],
CONSTRAINT [PK_sgip_submit] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
--存储过程--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetData]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SendSMS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SendSMS]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_SendSMS_Simple]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_SendSMS_Simple]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_getdata_safe]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_getdata_safe]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_sms_Recv_Deliver]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_sms_Recv_Deliver]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_sms_Recv_Report]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_sms_Recv_Report]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_sms_Recv_SubmitResp]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_sms_Recv_SubmitResp]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_sms_Research]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_sms_Research]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sms_init]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sms_init]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_sms_switchlog]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_sms_switchlog]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_GetData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Select statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select top 2 * into #tmp FROM sgip_submit Where SmsFlag=0 order by Priority asc,id asc
-- 直接使用会出现掉包
update sgip_submit set smsflag=6 where id in (select id from #tmp)
select * from #tmp
drop table #tmp
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--exec [sp_SendSMS] '5201','80520','13101234567',15,'业务代码,由SP定义',0,0,''
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_SendSMS]
@ServiceType varchar(10), --业务代码,由SP定义
@spNumber varchar(21), --sp接入号
@phone varchar(21), --用户手机号
@msgFormat int, --短消息的编码格式
@msgContent varchar(500), --短消息的内容
@MorelatetoMTFlag int, --引起MT消息的原因
@Priority int, --优先级0-9从低到高,默认为0
@linkID varchar(8)
AS
declare
@FeeType varchar(10), --计费类型
@FeeValue varchar(6), --该条短消息的收费值
@GivenValue varchar(6), --赠送用户的话费
@ReportFlag int, --状态报告标记
@CorpId varchar(5) --企业代码
BEGIN
set @CorpId='你自己的CPID'
set @ReportFlag=1
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Select statements.
SET NOCOUNT ON;
select @FeeType=FeeType,@FeeValue=FeeValue,@GivenValue=GivenValue from [Service] where
SPNumber=@spNumber
and
serverType=@ServiceType
if @FeeType is null
begin
print @spNumber
print @ServiceType
return
end
insert
into sgip_submit
(spNumber,ChargeNumber,UserNumber,CorpId,ServiceType,FeeType,FeeValue,GivenValue,AgentFlag,MorelatetoMTFlag,Priority,ExpireTime,ScheduleTime,ReportFlag,MessageCoding,MessageContent,linkID)
values
(@spNumber,@phone,@phone,@CorpId,@ServiceType,@FeeType,@FeeValue,@GivenValue,1,@MorelatetoMTFlag,@Priority,'','',@ReportFlag,@msgFormat,@msgContent,@linkID)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_SendSMS_Simple]
@number varchar(50),
@content nvarchar(160)
as
declare
@SPNumber varchar(21),
@serverType varchar(10)
SELECT TOP 1 @SPNumber=SPNumber, @serverType=serverType FROM [Service] ORDER BY id
-- 手机号国别处理
set @number = '86' + @number
-- 服务没开通
if @SPNumber is not null
begin
exec [sp_SendSMS] @serverType,@SPNumber,@number,15,@content,0,0,''
select 1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_getdata_safe]
@safe int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Select statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
exec('select top '+@safe + ' *
into #tmp FROM sgip_submit Where SmsFlag=0 or ( SmsFlag<>0 and
Priority > 0 ) order by Priority asc,id asc,SmsFlag asc update
sgip_submit set Priority=Priority + 1 where id in (select id from #tmp)
select * from #tmp drop table #tmp ')
--exec('select top '+@safe
+ ' * into #tmp FROM sgip_submit Where SmsFlag=0 order by Priority
asc,id asc update sgip_submit set Priority=Priority + 1 where id in
(select id from #tmp) select * from #tmp drop table #tmp ')
--exec('select top '+@safe + ' * FROM sgip_submit Where SmsFlag=0 order by Priority asc,id asc')
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_sms_Recv_Deliver]
@phone varchar(20), --用户手机号码
@spNumber varchar(50), --接收该短消息的SP的接入号码,字符
@msgFomart int, --短消息的编码格式
@MessageLength int, --短消息的长度
@msgContent varchar(500), --短消息的内容
@linkID varchar(8)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Select statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
insert into sgip_Deliver (phone,spNumber,MessageCoding,MessageLength,MessageContent,linkID,add_time) values
(@phone,@spNumber,@msgFomart,@MessageLength,@msgContent,@linkID,getdate())
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_sms_Recv_Report]
@SequenceNumber int,
@SequenceNumber1 int,
@SequenceNumber2 int,
@ReportType int, --Report命令类型
@phone varchar(20), --接收短消息的手机号,手机号码前加“86”国别标志
@State int, --该命令所涉及的短消息的当前执行状态
@ErrorCode int, --当State=2时为错误码值,否则为0
@Reserve int --保留,扩展用
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Select statements.
SET NOCOUNT ON;
insert into sgip_Report (SequenceNumber,SequenceNumber1,SequenceNumber2,ReportType,phone,[State],ErrorCode,Reserve,add_time)
values (@SequenceNumber,@SequenceNumber1,@SequenceNumber2,@ReportType,@phone,@State,@ErrorCode,@Reserve,getdate())
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_sms_Recv_SubmitResp]
@id int,
@result int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with Select statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
insert
into sgip_bill
(id,spNumber,ChargeNumber,UserNumber,UserCount,CorpId,ServiceType,FeeType,FeeValue,GivenValue,AgentFlag,MorelatetoMTFlag,Priority,ExpireTime,ScheduleTime,ReportFlag,TP_pid,TP_udhi,MessageCoding,MessageType,MessageContent,linkID,smsFlag,sendCount,lastSendTime,submit_add_time,result,add_time)
select
id,spNumber,ChargeNumber,UserNumber,UserCount,CorpId,ServiceType,FeeType,FeeValue,GivenValue,AgentFlag,MorelatetoMTFlag,Priority,ExpireTime,ScheduleTime,ReportFlag,TP_pid,TP_udhi,MessageCoding,MessageType,MessageContent,linkID,smsFlag,sendCount,lastSendTime,add_time,@result,getdate()
from sgip_submit where
id=@id
delete from sgip_submit where
id=@id
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: screen
-- ALTER date:
-- Description: 平台短信查询子系统
-- =============================================
CREATE PROCEDURE [sp_sms_Research]
@number varchar(50), --手机号
@searchID varchar(500),-- 查询代码
@xlspath varchar(400), -- xls表根目录
@noSearchNoBackInfo varchar(100),
@sheetname varchar(100)
as
declare
@ForSchoolsID nvarchar(50),
@ForMajorID nvarchar(50),
@ForClassesID nvarchar(50),
@BeginTime datetime,
@EndTime datetime,
@FilePath nvarchar(200),
@FitColum nvarchar(50) ,
@Content nvarchar(160),
@erroSMS nvarchar(160),
@totalRows int,
@studentTicket nvarchar(50)
BEGIN
set @erroSMS = ''
SET NOCOUNT ON;
SET @number = substring(@number,3, len(@number)-2)
-- 判断是否注册
SELECT @studentTicket=TicketNumber FROM sm_Students WHERE (UserID = @number)
if @studentTicket is null
begin
if @noSearchNoBackInfo <> 'true'
begin
set @erroSMS = '请注册开通!'
exec sp_SendSMS_Simple @number, @erroSMS
return
end
return
end
-- 查询方案获取
SELECT @ForSchoolsID=ForSchoolsID,
@ForMajorID=ForMajorID, @ForClassesID = ForClassesID,
@BeginTime=BeginTime, @EndTime=EndTime, @FilePath=FilePath,
@FitColum=FitColum,
@Content=Content
FROM sm_Ask WHERE (Number = @searchID)
if @Content is null
begin
-- 查询错误反馈
if @noSearchNoBackInfo = 'true'
begin
return;
end
set @erroSMS = '你的信息已经收到!'
exec sp_SendSMS_Simple @number, @erroSMS
return
end
if @BeginTime>getdate() or @EndTime<getdate()
begin
set @erroSMS = '你的查询不在有效期内'
exec sp_SendSMS_Simple @number, @erroSMS
return
end
-- 查询权限判断
if @ForSchoolsID <> '0' and @ForSchoolsID is not null
begin
SELECT @totalRows = COUNT(ID) FROM sm_Students WHERE (UserID = @number) AND (SchoolID = @ForSchoolsID)
if @totalRows < 1
begin
set @erroSMS = '你不在本查询所属'
exec sp_SendSMS_Simple @number, @erroSMS
return
end
else
begin
-- 不检查以下条件
set @ForMajorID = '0'
set @ForClassesID = '0'
end
end
else if @ForMajorID <> '0' and @ForMajorID is not null
begin
SELECT @totalRows = COUNT(ID) FROM sm_Students WHERE (UserID = @number) AND (MajorID = @ForMajorID)
if @totalRows < 1
begin
set @erroSMS = '你不在本查询
exec sp_SendSMS_Simple @number, @erroSMS
return
end
else
begin
-- 不检查以下条件
set @ForClassesID = '0'
end
end
else if @ForClassesID <> '0' and @ForClassesID is not null
begin
SELECT @totalRows = COUNT(ID) FROM sm_Students WHERE (UserID = @number) AND (ClassID = @ForClassesID)
if @totalRows < 1
begin
set @erroSMS = '你不在本查询
exec sp_SendSMS_Simple @number, @erroSMS
return
end
end
-- 获取xls数据
declare
@str nvarchar(500),
@newStr nvarchar(500),
@start int,
@i int,
@fi int,
@fn int,
@strColum nvarchar(500),
@strDeclare nvarchar(500)
set @str = @Content -- 设置短信格式内容
set @i = 0
set @fi = 1
set @fn = 1
set @strColum = ''
set @strDeclare = ''
while @fi <> 0
begin
set @fi = charindex ( '{' , @str , @i )
set @fn = charindex ( '}' , @str , @fi )
set @i = @fi+1
if(@fi <> 0)
begin
set @strColum = @strColum + ', ' + substring(@str,@fi+1,@fn-@fi-1)
end
end
if len(@strColum) > 0
begin
set @strColum = substring(@strColum,2,len(@strColum)-1)
end
-- 循环读取
declare
@tempColumn nvarchar(500),
@okStr nvarchar(500),
@tempValue nvarchar(500),
@tempSQL nvarchar(4000)
-- 关闭异常
set @fi = 1
set @fn = 1
while @fi <> 0 and @fi is not null
begin
set @fi = charindex ( '{' , @str , @i )
set @fn = charindex ( '}' , @str , @fi )
set @i = @fi+1
if(@fi <> 0 and @fi is not null)
begin
set @tempColumn = substring(@str,@fi+1,@fn-@fi-1);
-- set @tempSQL = 'SELECT TOP 1 @tempValue = ' + @tempColumn + ' FROM Orders WHERE (OrderID = 10248)' -- 'from #tempColums'
set
@tempSQL = ' SELECT TOP 1 @tempValue=' + @tempColumn + ' FROM
OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'', ''Data Source="'+@xlspath
+ @FilePath +'";Extended Properties= "Excel 8.0;HDR=YES;Excel
8.0";Persist Security Info=False '')...['+@sheetname+'$] WHERE ' +
@FitColum + '=''' + @studentTicket + ''''
--print @tempSQL
exec sp_executesql @tempSQL , N'@tempValue nvarchar(500) out',@tempValue = @tempValue out
set @str = REPLACE(@str,'{'+@tempColumn+'}',@tempValue)
end
end
-- 发送短信
exec [sp_SendSMS_Simple] @number,@str
SET ANSI_NULLS off
SET ANSI_WARNINGS on
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_sms_init]
as
-- update sgip_submit set smsflag=0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [sp_sms_switchlog]
@content varchar(500)
as
INSERT INTO sm_SwitchLog (SwitchTime, Content) VALUES (GetDate(), @Content)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
注: 原版请从http://www.dzend.com/ 上下载。我下载以后修改过地!
相关推荐
联通SGIP1.2短信网关,C#开发
联通SGIP短信协议SGIP1.2 sp网关开发必备
SGIP 1.2 联通短信网关模拟器 适用于联通 SGIP1.2协议短信开发 有图形界面,报文解析
联通SGIP1.2接口Java版实现(客户端与服务端)2013-10-23号已测试。 怎么部署里面有详细的说明或者去我的blog看
联通SGIP1.2网关协议联通SGIP1.2网关协议联通SGIP1.2网关协议联通SGIP1.2网关协议联通SGIP1.2网关协议联通SGIP1.2网关协议联通SGIP1.2网关协议
基于联通SGIP1.2协议编写的服务端和客户端短信平台,只要存在联通分配的IP、用户名、密码即可完成短信发送
1.接口说明 该接口库是用VC6.0开发的基于Windows平台上的SGIP1.2协议的实现,接口对中国联通SGIP协议进行了高度封装,目的是方便SP接入中国联通短消息网关。 2.功能特点 1)严格按照SGIP1.2协议实现,...
中国联通sgip1.2模拟网关,真实的环境,特别适合还没有申请到企业号的用户
目前中国联通使用的短信增值业务接口协议SGIP 1.2版,含详细协议包说明及错误说明。
SGIP1.2模拟网关 SGIP1.2模拟网关 SGIP1.2模拟网关SGIP1.2模拟网关
联通网关.sgip网关,sp接入.联通sgip网关
- 根据联通提供的SGIP1.2接口文档,封装协议,编写通信流程 - 下行短信(发送短信给手机用户),里面有两种方式发送短信,一种是Socket,一种是NIO,NIO的性能较高 - 上行短信(接收手机用户的短信),实际是接收联通短信中心...
java写的一个联通sgip1.2接收端程序
短信发送协议SGIP1.2
中国联通SGIP1.2 短消息业务联网协议
短消息业务联网协议(SGIP) 1.2版
中国联通 SGIP 1.2,为最新版协议.