十二分着急求大神解救

2013-02-19

万分着急，求大神解救啊USE DP6_SiemensGOALTERPROCEDURE [dbo].[Siemens_Confirm_XS]@BEGINTIME DATETIME

万分着急，求大神解救啊

USE DP6_Siemens
GO

ALTER  PROCEDURE [dbo].[Siemens_Confirm_XS]
        @BEGINTIME DATETIME ,
    @ENDTIME DATETIME,
    @CorpId INT
AS
    BEGIN

    --要发出的信息内容
INSERT INTO DP_SMSOUTQUEUE(CORPID , MOBILE , CONTENT , SendChannel , AttemptTimes , Operator)
        SELECT   CORPID=@CorpId ,
                dbo.GetMobileByMemberId(a.MemberId) AS MOBILE ,
                +'西门子dp平台核实：你好，你昨日共上报'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店的月进货额，总进货额'+CAST(ISNULL(a.BB,0)AS VARCHAR(10))+'元，已上报门店为：'+CAST(ISNULL(a.CC,0)AS VARCHAR(10))
                AS CONTENT ,
                dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),1) AS SENDCHANNEL ,
                0 AS ATTEMPTTIMES ,
                'Siemens_Confirm_XS' AS OPERATOR
     --内容的值
        FROM    ( SELECT    dce.MemberId ,
                     COUNT(DISTINCT CASE dce.Instruction
                          WHEN 'XS' THEN dce.StoreId
                                END) AA,

                                SUM(CASE dce.Instruction
                          WHEN 'XS' THEN CAST(dce.Param1 AS INT)
                                END) BB,

                        (SELECT NIMEI FROM (
SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI FROM (
SELECT AA.MemberId,
    (SELECT StoreId+' ' FROM dbo.DP_MemberStore  WHERE MemberId=AA.MemberId  FOR XML PATH('')) AS UserList

FROM dbo.DP_MemberStore AA
GROUP BY AA.MemberId
) B)L) CC

                  FROM      dbo.DP_CommonSmsEntries dce
                            LEFT JOIN dbo.DP_SmsInProcess SIP ON dce.NewSmsId = SIP.NewSmsId
                                                              AND dce.CorpId = SIP.CorpId
                            LEFT JOIN dbo.DP_Members c ON c.CorpId=dce.CorpId AND SIP.MemberId = c.MemberId
                            AND c.Status=2 AND c.RoleId=7
                  WHERE     dce.CorpId=@CorpId
                            AND dce.RecvTime >= @BEGINTIME
                            AND dce.RecvTime < @ENDTIME
                            AND dce.Instruction ='XS'
                  GROUP BY  dce.MemberId
                ) a
    END

GO

运行存储过程EXEC[dbo].[Siemens_Confirm_XS]
@BEGINTIME = N'2013-02-03',
@ENDTIME = N'2013-02-07',
@CorpId = 1

弹出提示：消息 512，级别 16，状态 1，过程 Siemens_Confirm_XS，第 10 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
语句已终止。

主要是FOR XML PATH('')输出东西，我想一个MenberId输出一列StorsId，可是貌似值的个数对不上，求大神帮助 xml
[解决办法]
我还是那个建议，你也不用额外写个临时表直接一句就ok


stuff((SELECT '店，'+cast(StoreId as nvarchar(50)) FROM dbo.DP_MemberStore WHERE MemberId=dce.MemberId  FOR XML PATH('')),1,2,'') as NIMEI

[解决办法]


SELECT CAST(StoreId AS NVARCHAR(10))+N'店，'

热点排行

SQL Server

十二分着急求大神解救