求一存储过程，关于定时循环更新的问题。

[lixiuchen]

注意表1和表2不能添加其它字段。

表1格式
ID 电话号码 信息内容 接收时间
869 13702350962 XGSR20010201 2011-1-20 10:03
870 13726138383 XGSR19800101 2011-1-25 12:01
880 13631763109 Xgsr19781127 2011-1-25 12:02
881 13521585425 XGSR20010211 2011-1-25 12:05

做一个带参数的存储过程，五分钟调用一次。
存储过程主要找到5分钟内收到的信息，通过电话号码做关联，将信息内容去掉前面的XGSR后转换成日期更新到另一表 表2。


表2格式：

ID 姓名 电话 生日
1 张三 13702350962 20010201
2 李四 13631763109 19781127
3 王五 13521585425 20010211

--------------------------------------------------------------------------------------------------------

** 本来想做触发器的，但服务器是移动提供的工业机，性能也不行，SQL功能也不全，做了触发就接收不到信息。

[快溜]

为啥要带参数的存储过程，你是怎么想的？

[liyingzi]

你要5分钟输入一次参数吗？还是参数=开始时间？

[高山]

楼主的意思应该就是参数就是从程序那儿传入的现在的时间吧?

[lixiuchen]

是不是带参数都无所谓了。
关键是更新数据。
如果说是把5分钟内的所有的电话号码相关的人员的生日改成一样的值就简单了。但这里是要循环一条一条地来修改相关电话号码的人员的生日。

如果5分钟有100人发送的自己的生日过来，就把这100人的生日更新到表2.

[AcHerat]

http://blog.csdn.net/LIUMRZY/archive/2009/06/10/4258338.aspx
看看这个博文，作业调度可以解决你的问题！

[AcHerat]

http://blog.csdn.net/LIUMRZY/archive/2009/06/10/4258338.aspx
看看这个博文，作业调度可以解决你的问题！

[高山]

存储过程这样写 楼主看可以不？

CREATE procedure [dbo].[mypr]
as
begin
	insert into 表2(ID,电话,生日) select ID,电话号码 as 电话,CAST(right(信息内容,8) as datetime) as 生日 from 表1 
    where 接收时间 > DATEADD(Minute,-5,getdate())
end

[lixiuchen]

--帖一下现有的存储过程。
--参数是用来决定轮询时间及其它的一些参数。
-- EXEC SP_SEND_SMS_SELL '5', 'T', ''
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_SEND_SMS_SELL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_SEND_SMS_SELL]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE [dbo].[SP_SEND_SMS_SELL]
@TIME VARCHAR(5), --轮询间隔(分钟)
@UNSUBSCRIBE VARCHAR(5), --是否发送退订信息 T(发送) 或 F(不发送)
@EX_MEMO VARCHAR(50) --附加信息
AS

DECLARE @MEMO VARCHAR(100) --退订信息
DECLARE @Unsubscrib VARCHAR(10) --取消订阅指令
DECLARE @REST_PASS_CODE VARCHAR(10) --恢复密码指令

SET @Unsubscrib = 'TD'
SET @REST_PASS_CODE = 'HFMM'

IF @UNSUBSCRIBE = 'T'
SET @MEMO = ',退订回复' + @Unsubscrib + ',谢谢!'
ELSE
SET @MEMO = '。'

INSERT INTO tbl_SMSendTask
(CreatorID, SmSendedNum, OperationType, SUBOPERATIONTYPE, Sendtype, OrgAddr, DestAddr,
SM_Content, SendTime, NeedStateReport, ServiceID, FeeType, FeeCode, SMType, MessageID, DestAddrType, SubTime,
TaskStatus, SendLevel, SendState, TryTimes, Count, SuccessID)
SELECT '0000' AS CreatorID, 0 AS SmSendedNum, 'WAS' AS OperationType, '66' AS SUBOPERATIONTYPE, 1 AS Sendtype, '106573010359' AS OrgAddr,
B.MOBILE_NUM AS DestAddr, (CASE WHEN B.FULL_NAME IS NULL THEN B.GIVENNAME ELSE B.FULL_NAME END)
+ (CASE WHEN C.DESCRIPTION IS NULL THEN '.' ELSE C.DESCRIPTION END)
+ ':您'+ CONVERT(VARCHAR(10), A.LAST_TRAN_DATE,21) + '日于'+ H.UNIT_NAME + '消费' + CONVERT(VARCHAR(20),A.LAST_TRAN_VAL)
+ '元,累计积分为:' + CONVERT(VARCHAR(20),ROUND(A.PNT_AVL,0)) + '分,' + @MEMO
+ (CASE WHEN @EX_MEMO IS NULL THEN '.' ELSE @EX_MEMO END) AS SM_Content, GETDATE() - 0.01 AS SendTime,
1 AS NeedStateReport , 'EIE' AS ServiceID, '02' AS FeeType, '0' AS FeeCode, '0' AS SMType, '0' AS MessageID, '0' AS DestAddrType,
GETDATE() AS SubTime, '0' AS TaskStatus, '2' AS SendLevel, '0' AS SendState, '3' AS TryTimes, '1' AS Count, '0' AS SuccessID
FROM DATA.MYDB.DBO.CUST A INNER JOIN DATA.MYDB.DBO.SYS_PRSNL B ON (A.CUST_ID = B.PRSNL_ID)
LEFT JOIN DATA.MYDB.DBO.SYS_UNIT F ON (A.SERVE_UNIT_ID = F.UNIT_ID)
LEFT JOIN (SELECT CODE, DESCRIPTION FROM DATA.MYDB.DBO.SYS_CODE_DTL WHERE CODE_TYPE = 'GENDER') C ON (C.CODE = B.GENDER)
LEFT JOIN DATA.MYDB.DBO.SYS_PRSNL_CLSF D ON (B.PRSNL_ID = D.PRSNL_ID)
LEFT JOIN DATA.MYDB.DBO.CUST_AVOC E ON (A.CUST_ID = E.CUST_ID)
INNER JOIN DATA.MYDB.DBO.CUST_CARD G ON (G.CUST_ID = A.CUST_ID)
INNER JOIN
( SELECT B.UNIT_NAME, A.CUST_ID, MAX(A.TRAN_TIME) AS LAST_TRAN_TIME
FROM DATA.MYDB.DBO.RLB A INNER JOIN DATA.MYDB.DBO.SYS_UNIT B ON (A.SHOP_ID = B.UNIT_ID)
WHERE A.CUST_ID IS NOT NULL GROUP BY B.UNIT_NAME, A.CUST_ID
) H ON (A.CUST_ID = H.CUST_ID)
WHERE D.PRSNL_TYPE = 'CU' AND D.STATUS = 'A' AND (A.LAST_TRAN_DATE IS NOT NULL) AND (B.MOBILE_NUM IS NOT NULL)
AND E.AVOC_TYPE = '00' AND DATEDIFF(MINUTE, H.LAST_TRAN_TIME, GETDATE()) <= @TIME
ORDER BY A.LAST_TRAN_DATE


--对系统人员登录密码进行重置
UPDATE DATA.MYDB.DBO.SYS_USER SET USER_PSWD = '?9>jKMR541TP'
WHERE USER_ID IN (
SELECT A.PRSNL_ID
FROM DATA.MYDB.DBO.SYS_PRSNL AS A INNER JOIN DATA.MYDB.DBO.SYS_PRSNL_CLSF AS B ON (A.PRSNL_ID = B.PRSNL_ID)
WHERE A.MOBILE_NUM IN (SELECT OrgAddr AS MOBILE_NUM FROM tbl_SMReceived
WHERE DATEDIFF(MINUTE, RecvTime, GETDATE()) <= @TIME AND SM_Content LIKE @REST_PASS_CODE+'%'
GROUP BY OrgAddr)
AND B.PRSNL_TYPE = 'US' AND A.MOBILE_NUM IS NOT NULL
)

--对顾客卡密码进行重置
UPDATE DATA.MYDB.DBO.CUST_CARD SET CARD_PSWD = NULL
WHERE CUST_ID IN (SELECT A.PRSNL_ID FROM DATA.MYDB.DBO.SYS_PRSNL AS A
INNER JOIN DATA.MYDB.DBO.SYS_PRSNL_CLSF AS B ON (A.PRSNL_ID = B.PRSNL_ID)
WHERE A.MOBILE_NUM IN (SELECT OrgAddr FROM tbl_SMReceived
WHERE DATEDIFF(MINUTE, RecvTime, GETDATE()) <= @TIME
AND SM_Content LIKE @REST_PASS_CODE+'%'
GROUP BY OrgAddr)
AND B.PRSNL_TYPE = 'CU' AND A.MOBILE_NUM IS NOT NULL
)

[lixiuchen]

表2的数据本来就有的，不是INSERT进去的。
是找到表1中以XGSR开头的信息的电话号码，再以电话号码做关联来更新表2里的生日。

[Quote=引用 7 楼 liu0701 的回复:]
存储过程这样写 楼主看可以不？

SQL code

CREATE procedure [dbo].[mypr]
as
begin
insert into 表2(ID,电话,生日) select ID,电话号码 as 电话,CAST(right(信息内容,8) as datetime) as 生日 from 表1
where 接收时间 > DATEADD(Minu……
[/Quote]

[高山]

那直接update 不行吗？

[lixiuchen]

[Quote=引用 10 楼 liu0701 的回复:]
那直接update 不行吗？
[/Quote]
是用UPDATE,就是多条记录，怎样来一条一条的来UPDATE?

[高山]

update 表2 
set 表2.生日 = c.生日 
from ( select ID,电话号码 as 电话,CAST(right(信息内容,8) as datetime) as 生日 from 表1 
    where 接收时间 > DATEADD(Minute,-5,getdate()) ) as c
where c.电话 = 表2.电话

[liang145]

[Quote=引用 11 楼 lixiuchen 的回复:]
引用 10 楼 liu0701 的回复:
那直接update 不行吗？

是用UPDATE,就是多条记录，怎样来一条一条的来UPDATE?
[/Quote]

用游标可以么？

[zhufobbs]

用作业 JOB 调用存储过程，时间间隔为5分钟

[lixiuchen]

[Quote=引用 12 楼 liu0701 的回复:]
SQL code

update 表2
set 表2.生日 = c.生日
from ( select ID,电话号码 as 电话,CAST(right(信息内容,8) as datetime) as 生日 from 表1
where 接收时间 > DATEADD(Minute,-5,getdate()) ) as c
where c.电话 = 表2.电话
[/Quote]

晕了，where c.电话 = 表2.电话
这里，前缀超过3个