ALTER PROCEDURE [dbo].[ZSalesInfosByTypeDayPage]
@StartTime datetime,
@EndTime datetime,
@PageSize INT=10,
@PageNum INT=1,
@Type varchar(50)='A'AS
BEGIN
SET NOCOUNT ON;
CREATE FUNCTION fn_TempTable(@StartTime datetime,@EndTime datetime,@Type varchar(50))
RETURNS table
ASdeclare @sql varchar(8000)
set @sql='SELECT PatternNumBer,proimg, ShopType, Size, Color,CONVERT(datetime, Time) AS SalesTime, SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos a inner join ml_product b on a.patternnumber=b.procode
WHERE (SUBSTRING(PatternNumBer, 1, 1) ='''+convert(varchar,@Type)+''' and datediff(day,CONVERT(datetime, Time),'''+convert(varchar,@StartTime)+''')<=0 and datediff(day,CONVERT(datetime, Time),'''+convert(varchar,@EndTime)+''')>=0 )
GROUP BY PatternNumBer,proimg, ShopType, Size, Color, CONVERT(datetime, Time)
order by PatternNumBer,proimg, ShopType, Size, Color,SalesTime asc' RETURN (exec(@sql)) declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([SalesTime])+'=max(case when [SalesTime]='+quotename([SalesTime],'''')+' then [num] else 0 end)'
from fn_TempTable(@StartTime,@EndTime,@Type) group by[SalesTime]declare @newSql varchar(4000)
set @newSql='select PatternNumBer,proimg, ShopType, Size,Color'+@s+',[总计]=sum([num]) from fn_TempTable('''+@StartTime+''','''+@EndTime+''','''+@Type+''') group by PatternNumBer,proimg, ShopType, Size, Color'
exec(@newSql)
END
go
注意在SQL SERVER2000下
消息 156,级别 15,状态 1,过程 ZSalesInfosByTypeDayPage,第 12 行
在关键字 'FUNCTION' 附近有语法错误。
消息 156,级别 15,状态 1,过程 ZSalesInfosByTypeDayPage,第 23 行
在关键字 'exec' 附近有语法错误。
消息 170,级别 15,状态 1,过程 ZSalesInfosByTypeDayPage,第 23 行
第 23 行: ')' 附近有语法错误。
@StartTime datetime,
@EndTime datetime,
@PageSize INT=10,
@PageNum INT=1,
@Type varchar(50)='A'AS
BEGIN
SET NOCOUNT ON;
CREATE FUNCTION fn_TempTable(@StartTime datetime,@EndTime datetime,@Type varchar(50))
RETURNS table
ASdeclare @sql varchar(8000)
set @sql='SELECT PatternNumBer,proimg, ShopType, Size, Color,CONVERT(datetime, Time) AS SalesTime, SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos a inner join ml_product b on a.patternnumber=b.procode
WHERE (SUBSTRING(PatternNumBer, 1, 1) ='''+convert(varchar,@Type)+''' and datediff(day,CONVERT(datetime, Time),'''+convert(varchar,@StartTime)+''')<=0 and datediff(day,CONVERT(datetime, Time),'''+convert(varchar,@EndTime)+''')>=0 )
GROUP BY PatternNumBer,proimg, ShopType, Size, Color, CONVERT(datetime, Time)
order by PatternNumBer,proimg, ShopType, Size, Color,SalesTime asc' RETURN (exec(@sql)) declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([SalesTime])+'=max(case when [SalesTime]='+quotename([SalesTime],'''')+' then [num] else 0 end)'
from fn_TempTable(@StartTime,@EndTime,@Type) group by[SalesTime]declare @newSql varchar(4000)
set @newSql='select PatternNumBer,proimg, ShopType, Size,Color'+@s+',[总计]=sum([num]) from fn_TempTable('''+@StartTime+''','''+@EndTime+''','''+@Type+''') group by PatternNumBer,proimg, ShopType, Size, Color'
exec(@newSql)
END
go
注意在SQL SERVER2000下
消息 156,级别 15,状态 1,过程 ZSalesInfosByTypeDayPage,第 12 行
在关键字 'FUNCTION' 附近有语法错误。
消息 156,级别 15,状态 1,过程 ZSalesInfosByTypeDayPage,第 23 行
在关键字 'exec' 附近有语法错误。
消息 170,级别 15,状态 1,过程 ZSalesInfosByTypeDayPage,第 23 行
第 23 行: ')' 附近有语法错误。
我上面的错知道了,是return 后面不是大括号,而是小括号,我这样的做法主要是代替带参视图