我知道这样不好，但实在没办法，那么长的一段东西，求能帮忙的大神
USE [DP6_Siemens]
GO
/****** Object: StoredProcedure [dbo].[Siemens_Confirm_JH] Script Date: 04/09/2013 09:08:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Siemens_Confirm_JH]
@BeginTime VARCHAR(10) ,
@EndTime VARCHAR(10) ,
@Corpid INT
AS
BEGIN
--建立一个临时表，用于存放每天计划任务的内容
CREATE TABLE #abc
(
a VARCHAR(MAX) ,
b VARCHAR(MAX) ,
c VARCHAR(MAX) ,
d INT
)
--循环，求这周JH任务的数量，提取每天编号
DECLARE @Commons INT ,
@index INT ,
@commonid INT ,
@memberid INT
SET @index = 1
SELECT @Commons = COUNT(DISTINCT a.CommonId)
FROM dbo.DP_CommonSmsEntries a
WHERE a.corpid = @Corpid
AND a.RecvTime >= @BeginTime
AND a.RecvTime < @EndTime
AND Instruction = 'JH'
CREATE TABLE #Commons
(
CommonId VARCHAR(50) ,
memberid INT
)
INSERT INTO #Commons
SELECT DISTINCT
CommonId ,
MemberId
FROM DP_CommonSmsEntries a
WHERE a.corpid = @Corpid
AND a.RecvTime >= @BeginTime
AND a.RecvTime < @EndTime
AND Instruction = 'JH'
--select * from #Commons
WHILE @index <= @Commons
BEGIN
SELECT @commonid = CommonId
FROM #Commons
SELECT @memberid = MemberId
FROM dbo.DP_CommonSmsEntries
WHERE CommonId = @commonid
--把每天的JH任务内容放进临时表中
INSERT INTO #abc
SELECT ( SELECT CASE WHEN a.Param1 = b.Code
THEN b.Description
END AS a
FROM dbo.DP_CommonSmsEntries a
LEFT JOIN dbo.DP_Code_Config_Tbl b ON a.CorpId = b.CorpId
AND Field_Order = 2
AND b.Sms_Style_Id = 'JH'
AND a.Param1 = b.Code
LEFT JOIN dbo.DP_Members c ON a.MemberId = c.MemberId
AND a.CorpId = c.CorpId
WHERE c.Status = 2
AND c.RoleId = 7
AND a.CommonId IN ( @commonid )
--AND a.MemberId IN (@memberid)
) AS a ,
( SELECT '，上午'
+ CASE WHEN a.Param2 = b.Code
THEN b.Description
END AS b
FROM dbo.DP_CommonSmsEntries a
LEFT JOIN dbo.DP_Code_Config_Tbl b ON a.CorpId = b.CorpId
AND Field_Order = 3
AND b.Sms_Style_Id = 'JH'
AND a.Param2 = b.Code
LEFT JOIN dbo.DP_Members c ON a.MemberId = c.MemberId
AND a.CorpId = c.CorpId
WHERE c.Status = 2
AND c.RoleId = 7
AND a.CommonId IN ( @commonid )
--AND a.MemberId IN (@memberid)
) AS b ,
( SELECT '，下午'
+ CASE WHEN a.Param4 = b.Code
THEN b.Description
END + '。' AS c
FROM dbo.DP_CommonSmsEntries a
LEFT JOIN dbo.DP_Code_Config_Tbl b ON a.CorpId = b.CorpId
AND Field_Order = 5
AND b.Sms_Style_Id = 'JH'
AND a.Param4 = b.Code
LEFT JOIN dbo.DP_Members c ON a.MemberId = c.MemberId
AND a.CorpId = c.CorpId
WHERE c.Status = 2
AND c.RoleId = 7
AND a.CommonId IN ( @commonid )
--AND a.MemberId IN (@memberid)
) AS c ,
( SELECT a.memberid
FROM dbo.DP_CommonSmsEntries a
WHERE a.CommonId IN ( @commonid )
--AND a.MemberId IN (@memberid)
) AS d
--SELECT * from #abc
-- DELETE #Commons
-- WHERE CommonId = @CommonId
-- SET @index += 1
--END
--插入发送队列表
/** INSERT INTO DP_SmsOutQueue
( CorpId ,
Mobile ,
[Content] ,
SendChannel ,
AttemptTimes ,
Operator
)**/
SELECT @Corpid AS Corpid ,
dbo.GetMobileByMemberId(@memberid) AS Mobile ,
'平台核实：你好，平台已登记你本周的工作计划。'
+ ( SELECT '' + a + b + c
FROM #abc
WHERE #abc.d --IN ( @memberid )
IN (
SELECT DISTINCT
a.MemberId
FROM dbo.DP_CommonSmsEntries a
WHERE a.CommonId IN ( @commonid ) )
FOR
XML PATH('')
) + '祝你工作愉快！' AS CONTENT ,
dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(@memberid),
@Corpid) AS SendChannel ,
0 AS AttemptTimes ,
'Siemens_Confirm_JH' AS Operator
DELETE #Commons
WHERE CommonId = @CommonId
SET @index += 1
END
DROP TABLE #abc
DROP TABLE #Commons
END
-- EXEC [dbo].[Siemens_Confirm_JH] '2013-03-20','2013-03-21','1'
循环 select 