一种对ntext类型的字段的操作,希望在要用的时候对你有所帮助 ---Haibing,来自MailService Platform
DECLARE @SCHEDULE_TIME_GROUP_BASE DATETIME, --组定时值
@MAIL_ID_GROUP_LIST VARCHAR(500), --合并组后的MAIL_ID列表,‘,’分隔
@MAIL_ID_GROUP_BASE VARCHAR(10),
@CONTENT_GROUP_BASE_PTR VARBINARY(16),
@ATTACHMENT_GROUP_BASE_PTR VARBINARY(16)
SELECT TOP 1
@SCHEDULE_TIME_GROUP_BASE =SCHEDULE_TIME, --该组内最早的定时(有排序)
@MAIL_ID_GROUP_BASE =MAIL_ID,
@CONTENT_GROUP_BASE_PTR =TEXTPTR([CONTENT]),
@ATTACHMENT_GROUP_BASE_PTR =TEXTPTR(ATTACHMENT)
FROM
#MS_MAIL_POOL_GROUP
ORDER BY
SCHEDULE_TIME ASC --按SCHEDULE_TIME排序
--该组是否满足定时条件
IF (@SCHEDULE_TIME_GROUP_BASE<=GETDATE()) --该组内最早的定时已到,可以合并发送该组
BEGIN
----------------------------------------------------------------------
--开始合并
--列表第一项
SET @MAIL_ID_GROUP_LIST=CONVERT(VARCHAR(10),@MAIL_ID_GROUP_BASE)
--对于要合并的数据,定义辅助合并的变量
DECLARE @MAIL_ID_GROUP_DETAIL VARCHAR(10),
@CONTENT_GROUP_DETAIL_PTR VARBINARY(16),
@ATTACHMENT_GROUP_DETAIL_PTR VARBINARY(16)
DECLARE P_GROUP_DETAIL CURSOR FOR
SELECT
MAIL_ID,
TEXTPTR([CONTENT]),
TEXTPTR(ATTACHMENT)
FROM
#MS_MAIL_POOL_GROUP
WHERE
MAIL_ID<>@MAIL_ID_GROUP_BASE --循环其它待合并项
OPEN P_GROUP_DETAIL
FETCH NEXT FROM P_GROUP_DETAIL INTO
@MAIL_ID_GROUP_DETAIL,
@CONTENT_GROUP_DETAIL_PTR,
@ATTACHMENT_GROUP_DETAIL_PTR
WHILE(@@FETCH_STATUS=0)
BEGIN
--合并
SET @MAIL_ID_GROUP_LIST=@MAIL_ID_GROUP_LIST+','+@MAIL_ID_GROUP_DETAIL
UPDATETEXT #MS_MAIL_POOL_GROUP.[CONTENT] @CONTENT_GROUP_BASE_PTR NULL 0 #MS_MAIL_POOL_GROUP.[CONTENT] @CONTENT_GROUP_DETAIL_PTR
UPDATETEXT #MS_MAIL_POOL_GROUP.ATTACHMENT @ATTACHMENT_GROUP_BASE_PTR NULL 0 '|' --分隔符号
UPDATETEXT #MS_MAIL_POOL_GROUP.ATTACHMENT @ATTACHMENT_GROUP_BASE_PTR NULL 0 #MS_MAIL_POOL_GROUP.ATTACHMENT @ATTACHMENT_GROUP_DETAIL_PTR
--下一项
FETCH NEXT FROM P_GROUP_DETAIL INTO
@MAIL_ID_GROUP_DETAIL,
@CONTENT_GROUP_DETAIL_PTR,
@ATTACHMENT_GROUP_DETAIL_PTR
END
CLOSE P_GROUP_DETAIL
DEALLOCATE P_GROUP_DETAIL
END