执行数据透视存储过程时得到的预期错误

This is my stored procedure:

这是我的存储过程:

ALTER procedure [dbo].[performancepivot]   
    @startdate nvarchar(100), @enddate nvarchar(100) 
AS
BEGIN
    declare @date1 nvarchar(100) = convert(varchar, @startdate+' 00:00:00.000', 120)  
    declare @date2 nvarchar(100) = convert(varchar, @enddate+' 23:59:59.000', 120)   
    DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)     

    select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Vtype)
                            from VType_tbl   
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')  ,1,1,'')       
    set @query = 'SELECT LocName, ' + @cols
                + '(select l.LocName
                          ,v.Vtype
                          ,[dbo].[testfunctionstacknew](CONVERT(decimal(10,1)
                                                        ,AVG(CONVERT(NUMERIC(18,2)
                                                        ,DATEDIFF(SS,t.Paydate,t.DelDate))))) as Average    
                    from (select l.LocName
                                ,Vtype
                          from Transaction_tbl t
                          join VType_tbl v on t.vtid = v.vtid
                          join dbo.Location_tbl l on t.locid=l.Locid
                          where dtime between '''+ @date1 +''' and '''+ @date2 +'''    
                          and Status = 5) d 

                          pivot 

                          (count(Vtype) for Vtype in (' + @cols + ')) p '   
    print @query
    exec sp_executesql @query;  
end

While executing this, I am getting Error like this:

在执行此操作时,我收到如下错误:

SELECT LocName
      ,[Emaar Staff]
      ,[Lost Ticket]
      ,[Normal]
      ,[VIP]
      ,[VVIP]
      , -- This Comma was missing added by MBD edit
      (select l.LocName
             ,v.Vtype
             ,[dbo].[testfunctionstacknew](CONVERT(decimal(10,1)
                                          ,AVG(CONVERT(NUMERIC(18,2)
                                            ,DATEDIFF(SS,t.Paydate,t.DelDate))))) as Average
from (select l.LocName
            ,Vtype
      from Transaction_tbl t
      join VType_tbl v on t.vtid = v.vtid
      join dbo.Location_tbl l on t.locid=l.Locid
      where dtime between '2013-01-01 00:00:00.000' and '2013-08-01 23:59:59.000'  
            and Status = 5) d

      pivot  

      (count(Vtype) for Vtype in ([Emaar Staff],[Lost Ticket],[Normal],[VIP],[VVIP])) p

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'p'. (1 row(s) affected)

消息102,级别15,状态1,行8'p'附近的语法不正确。 (1排受影响)

What is wrong with my stored procedure?

我的存储过程有什么问题?

1 个解决方案

#1

Your query is suffering from structural and syntax errors:

您的查询遇到结构和语法错误:

I got lost trying to keep up with the number of opened and closed ( ). You should start looking at that.

我迷路了,想要跟上打开和关闭的数量()。你应该开始考虑那个。

You cannot use an embedded SELECT statement within an other SELECT. Embedded SELECT can only be using within a FROM, WHERE or HAVING statement. In your query, the sub select is returning 3 values but you are referencing them as Average (one value)

您不能在其他SELECT中使用嵌入的SELECT语句。嵌入式SELECT只能在FROM,WHERE或HAVING语句中使用。在您的查询中,子选择返回3个值,但您将它们称为平均值(一个值)

I highly recommend you write the stored procedure directly as I can see you are using the query construction to embed variables only, not to actually build the query. you can write the query as a usual stored procedure and use them as SP variables instead.

我强烈建议您直接编写存储过程,因为我可以看到您使用查询构造仅嵌入变量,而不是实际构建查询。您可以将查询编写为通常的存储过程,并将其用作SP变量。

I added a missing comma in your query, please check it

我在您的查询中添加了一个丢失的逗号,请检查它

#1