ALTER PROCEDURE upReportPeriod
@PlaceID INT,
@CompareType INT --比较类型 1:时段 2:天 3:周 4:月 5:年
AS
DECLARE @ColName VARCHAR(500)
IF (@CompareType = 1)
BEGIN
SET @ColName = '''Hour''+:00' --Hour为字段
END
EXEC('SELECT '+@ColName+', SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = @PlaceID
GROUP BY '+ @ColName)
@PlaceID INT,
@CompareType INT --比较类型 1:时段 2:天 3:周 4:月 5:年
AS
DECLARE @ColName VARCHAR(500)
IF (@CompareType = 1)
BEGIN
SET @ColName = '''Hour''+:00' --Hour为字段
END
EXEC('SELECT '+@ColName+', SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = @PlaceID
GROUP BY '+ @ColName)
@PlaceID INT,
@CompareType INT --比较类型 1:时段 2:天 3:周 4:月 5:年
AS
DECLARE @ColName VARCHAR(500)
IF (@CompareType = 1)
BEGIN
SET @ColName = '[''Hour''+:00]' --Hour为字段
END
EXEC('SELECT '+@ColName+', SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = @PlaceID
GROUP BY '+ @ColName)
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = @PlaceID
GROUP BY '+ @ColName)或者将WHERE AdverPlaceID = @PlaceID GROUP BY '+ @ColName)
修改为 WHERE AdverPlaceID = ' + @PlaceID +' GROUP BY '+ @ColName)
运行[dbo].[upReportPeriod] ( @PlaceID = 11, @CompareType = 1 ).必须声明标量变量 "@PlaceID"。
没有行受影响。
(返回 0 行)
@RETURN_VALUE = 0
完成 [dbo].[upReportPeriod] 运行。ALTER PROCEDURE upReportPeriod
@PlaceID INT,
@CompareType INT --比较类型 1:时段 2:天 3:周 4:月 5:年
AS
DECLARE @ColName VARCHAR(500)
IF (@CompareType = 1)
BEGIN
SET @ColName = '[''Hour''+:00]' --Hour为字段
END
EXEC('SELECT '+@ColName+', SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = '+@PlaceID+' GROUP BY '+ @ColName)
改成这个后,提示错误:
运行[dbo].[upReportPeriod] ( @PlaceID = 11, @CompareType = 1 ).列名 ''Hour'+:00' 无效。
列名 ''Hour'+:00' 无效。
没有行受影响。
(返回 0 行)
@RETURN_VALUE = 0
完成 [dbo].[upReportPeriod] 运行。用云中客的方法后,提示错误如下:
运行[dbo].[upReportPeriod] ( @PlaceID = 11, @CompareType = 1 ).':' 附近有语法错误。
没有行受影响。
(返回 0 行)
@RETURN_VALUE = 0
完成 [dbo].[upReportPeriod] 运行。
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = '+rtrim(@PlaceID)+'
GROUP BY '+ @ColName)
我改成这样的了,可还是有错误提示如下:
运行[dbo].[upReportPeriod] ( @PlaceID = 11, @CompareType = 1 ).列名 ''Hour'+:00' 无效。
列名 ''Hour'+:00' 无效。
没有行受影响。
(返回 0 行)
@RETURN_VALUE = 0
完成 [dbo].[upReportPeriod] 运行。
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = '+rtrim(@PlaceID)+'
GROUP BY '+ @ColName)
BEGIN
SET @ColName = '''Hour''+:00' --Hour为字段
END是做什么的?Hour为字段名,而你试图从表中取得''Hour'+:00' 字段,当然会出错
ALTER PROCEDURE upReportPeriod
@PlaceID INT,
@CompareType INT--比较类型1:时段 2:天 3:周 4:月 5:年
AS
EXEC(
'select ' + case when @CompareType = 1 then 'Hour + '':00''' else 'Hour' end + ' as Hour,
SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = ' + cast(@PlaceID as varchar(10)) +
' GROUP BY Hour')
go
----------------------------------------------------------------------------------
当二个参数均为1是,生成的SQL字符串是这样的,看看是否符合楼主的要求:
select Hour + ':00' as Hour,
SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = 1 GROUP BY Hour
按照上面的方法提示错误信息:
关键字“case”附近有语法错误
Hour字段存入的是1、2、3、4、5、6.....之类的字符,是数值型的。输出时变成1:00-2:00、2:00-3:00....
定义变量DECLARE @ColName VARCHAR(500)是因为出现的可能结果会很多,而用Case When的形式,代码会非常长,不易查找错误。所以要根据@CompareType值的不同,生成不同的@ColName,@ColName会包含不同的字段名
BEGIN
SET @ColName = '''Hour''+:00' -Hour为字段
END
此处的Hour是什么,如果是字段,你也没有查询表,如何得来的
如果是变量,也没有赋值
如果是字符,好像没有什么意义吧
BEGIN
SET @ColName = 'Hour' +''':00'''
END
ALTER PROCEDURE upReportPeriod
@PlaceID INT,
@CompareType INT--比较类型1:时段 2:天 3:周 4:月 5:年
AS
declare @sql varchar(4000)
set @sql = 'select ' + case when @CompareType = 1 then 'cast(Hour as varchar(500)) + '':00''' else 'Hour' end + ' as Hour,
SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = ' + cast(@PlaceID as varchar(10)) +
' GROUP BY Hour'
EXEC(@sql)
go
BEGIN
SET @ColName = 'Hour + '':00'''
END
Hour是字段按照:
IF (@CompareType=1)
BEGIN
SET @ColName = 'Hour' +''':00'''
END提示有错误:运行[dbo].[upReportPeriod] ( @PlaceID = 11, @CompareType = 1 ).':00' 附近有语法错误。
没有行受影响。
(返回 0 行)
@RETURN_VALUE = 0
完成 [dbo].[upReportPeriod] 运行。表的结构如下:
AdverTrack
AdverTrackID int Unchecked
OrderItemID int Unchecked
PageViewCount int Unchecked
ClickCount int Unchecked
Date datetime Checked
Hour int Checked
OrderItem
OrderItemID int Unchecked
Name nvarchar(20) Checked
AdverPlaceID int Unchecked
@PlaceID INT,
@CompareType INT--比较类型1:时段 2:天 3:周 4:月 5:年
AS
declare @sql varchar(4000)
set @sql = 'select ' + case when @CompareType = 1 then 'cast(Hour as varchar(500)) + '':00''' else 'Hour' end + ' as Hour,
SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = ' + cast(@PlaceID as varchar(10)) +
' GROUP BY Hour'
EXEC(@sql)这个可以,我再看看转成变量,感谢
@PlaceID INT,
@CompareType INT --比较类型 1:时段 2:天 3:周 4:月 5:年
AS
DECLARE @ColName VARCHAR(500)
IF (@CompareType = 1)
BEGIN
SET @ColName = 'cast(Hour as varchar(500)) + '':00'''
END
EXEC('SELECT '+@ColName+', SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = '+@PlaceID+'GROUP BY '+ @ColName)这样就可以了
BEGIN
SET @ColName = 'Hour + '':00'''
END
主要是在这里把大家搞晕了
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = @PlaceID
GROUP BY '+ @ColName)=========
改成:EXEC('SELECT '+ @ColName +', SUM(AdverTrack.PageViewCount) 曝光量, SUM(AdverTrack.ClickCount) 点击量
FROM OrderItem
Inner Join AdverTrack ON OrderItem.OrderItemID = AdverTrack.OrderItemID
WHERE AdverPlaceID = '+ cast( @PlaceID as int) +
' GROUP BY '+ @ColName)