如何解决此存储过程（4周记录显示） [英] How Can I Solve This Stored Procedure (4 Week Record Display)

问题描述

i想要在插入日期后显示4周记录的stroed程序

我编写了这个存储过程

但是我收到错误

i want to create a stroed procedure for display 4 week records after insert date
and i wrote this stored procedure
but i got an error

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'group'.

我认为这个错误在第二次循环后发生。

请检查我的SP并给我一个解决方案

and i think this error occured after second while loop.
please check my S.P and give me a solution for this

--exec [AA_SP_Weekly] '2010-08-28'

ALTER procedure [dbo].[AA_SP_Weekly]
 @startingDate datetime
as
begin

  declare @numWeeks int
  declare @selectClause varchar(8000)
  declare @fromClause varchar(8000)
  declare @whereClause varchar(8000)
  declare @groupByClause varchar(8000)
  declare @i int;
  declare @sDate datetime
  declare @colHead varchar(255)
  declare @case varchar(1000)
  declare @cases varchar(8000)
  declare @sqlInner varchar(8000)

  set @numWeeks = 4  
  set @i = 0;
  set @cases = ''
  
-- while loop start
  while (@i < @numWeeks)
  begin
    set @sDate = @startingDate + (@i * 7)
    set @colHead = '[Week' + CONVERT(varchar(2), @i) + ']'
set @case = ', case when StartDate >= ''' + CONVERT(varchar(30),@sDate,101) + '''and StartDate < ''' + CONVERT(varchar(30), @sDate + 7, 101) + ''' then COUNT(CourseName) else 0 end'
    
    set @cases = @cases + '' +@case + ' as ' + @colHead
    set @i = @i + 1
  end  
-- while loop end
  
  set @selectClause = 'select CourseName' + @cases   
  set @fromClause = 'from GL_BookingList bl inner join GL_MasterPriceDefaultSelection mpds on bl.PriceItemId=mpds.PriceItemId inner join GL_Course on GL_Course.CourseID=mpds.SelectionId'
 
 set @whereClause = ' where (GL_Course.StartDate >= '''+ CONVERT(varchar(30),@startingDate, 101) + ''' and GL_Course.StartDate < ''' + CONVERT(varchar(30), @startingDate + (@numWeeks * 7), 101) + ''')'
 
  set @sqlInner = @selectClause + @fromClause + @whereClause
  
     --print @sqlInner

  set @i = 0;
  set @cases = ''
  
-- while loop start
  while (@i < @numWeeks)
  begin
    set @sDate = @startingDate + (@i * 7)
    set @colHead = '[Week of ' + CONVERT(varchar(255), @sDate, 6) + ']'
    set @case = ', Sum([Week' + CONVERT(varchar(2), @i) + '])'
    
    set @cases = @cases + '' +@case + ' as ' + @colHead
    set @i = @i + 1
  end  
-- while loop end

  set @selectClause = 'select CourseName' + @cases
  set @fromClause = '  from (' + @sqlInner + ')'
  set @groupByClause = ' group by CourseName,GL_Course.StartDate'  

	print (@selectClause + @fromClause + @groupByClause)
	execute(@selectClause + @fromClause + @groupByClause)
end
--exec [AA_SP_Weekly] '2010-08-20'

提前谢谢

推荐答案

在您生成的查询中，您需要将内部查询命名为：

In the query you produce you need to name the inner query like this:

SELECT ...
FROM (
	...
	) x -- name it yourself
GROUP BY CourseName,GL_Course.StartDate

即使你没有在外部查询中明确引用它，内部查询也需要有一个别名。



编辑



在你的代码中你需要改变这个：

The inner query needs to have an alias even if you don't reference it explicitly in the outer query.

Edit

In your code you need to change this:

set @fromClause = '  from (' + @sqlInner + ') x'

这篇关于如何解决此存储过程（4周记录显示）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！