我知道这样不好，但实在没办法，那么长的一段东西，求能帮忙的大神

Question

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
GOALTER 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

Answer 1

你的这个代码只有赋值和插入，不会有select 的啊，你是不是注释了什么？

Answer 2

有啊，这段  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 ,

Answer 3

你把这段移出while循环外面去吧。没看到你的insert是注销了

Answer 4

insert是产生作用的下一步，在先阶段不需要嘛，所以就注销了，先select看效果就好了。把这段放出来放在前面后面都不起作用啊，我想是不是这个循环不适合这个

Answer 5

把select那里移出循环之后只出现一个号码的：1 10086 平台核实：你好，平台已登记你本周的工作计划。星期一，上午公司会议，下午项目拜访。星期五，上午休假，下午门店拜访。星期四，上午促销活动，下午出差。星期三，上午Seminar，下午代理商沟通。星期二，上午公司会议，下午培训。星期一，上午门店拜访，下午项目拜访。祝你工作愉快！ 3 0 Siemens_Confirm_JH

Answer 6

         --把每天的JH任务内容放进临时表中                 
INSERT  INTO #abc
  SELECT  (  ---我的说明:这内面有问题,因为a,b,c,d没有必然的联系,对不齐的,资料就会多了,有点象行列转换,一行的多列,要有共同的归属!