set @sqlpage='select top '+@pagesize+' * from #temp
where #temp.id not in(select top (('+@pagecount+'-1)* '+@pagesize+')#temp.id from #temp) order by #temp.id'
@pagesize和@pagecount分别是分页中每页显示的条数和分页中当前页数,都是int型。以上的写法调用此存储过程后会报错,错误如下:
消息 245,级别 16,状态 1,过程 sp_yearlichengReport,第 71 行
在将 varchar 值 'select top ' 转换成数据类型 int 时失败。
where #temp.id not in(select top (('+@pagecount+'-1)* '+@pagesize+')#temp.id from #temp) order by #temp.id'
@pagesize和@pagecount分别是分页中每页显示的条数和分页中当前页数,都是int型。以上的写法调用此存储过程后会报错,错误如下:
消息 245,级别 16,状态 1,过程 sp_yearlichengReport,第 71 行
在将 varchar 值 'select top ' 转换成数据类型 int 时失败。
解决方案 »
- Sql 一个时间段和另一个时间段比较
- 求一条SQL语句,大家都进来帮忙看看
- 一个sql count 的问题
- 关键字 'union' 附近有语法错误。
- 有三个表,把它们联合后汇总,我要根据用户的选择来确定汇总的分类字段、汇总字段、条件,请教!
- 求一搜索的全过程,我的数据库搜索后符合条件的记录有10万条,现在什么都没用,速度非常慢,请指点一下这样的解决方法?
- 将varchar 值转换为数据类型 tinyint怎么转换
- 如何查询一个字段的值为空即null
- How can I get the result using sql
- ASP课程设计:学校设备管理系统!时间有点赶
- 关于FROM后加查询结果的问题
- sql2000 update 随机更新问题。
set @sqlpage='select top '+ltrim(@pagesize)+' * from #temp
where #temp.id not in(select top (('+ltrim(@pagecount)+'-1)* '+ltrim(@pagesize)+')#temp.id from #temp) order by #temp.id'
where #temp.id not in(select top (('+ltrim(@pagecount)+'-1)* '+ltrim(@pagesize)+')#temp.id from #temp) order by #temp.id'
我试过你说的方法,用以上代码调用这个存储过程,
报错:
消息 102,级别 15,状态 1,第 3 行
'10' 附近有语法错误。
set @sqlpage='select top '+Ltrim(@pagesize)+' * from #temp
where #temp.id not in(select top (('+Ltrim(@pagecount)+'-1)* '+Ltrim(@pagesize)+')#temp.id from #temp) order by #temp.id'
where #temp.id not in(select top (('+ltrim(@pagecount)+'-1)* '+ltrim(@pagesize)+')#temp.id from #temp) order by #temp.id'
print(@sqlpage)--把上面语句的显示结果自己看一下,或者发上来
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_yearlichengReport]
@yeartime varchar(10),------------------------------------------时间参数
@pagesize int,--------------------------------------------------分页中每页显示的信息条数
@pagecount int--------------------------------------------------分页中当前页数
as
begin
declare @sql varchar(8000)-------------------------------------查询的操作语句
declare @sqlpage varchar(8000)-------------------------------------------查询分页的操作语句
create table #temp
(
id int identity(1,1) not null,----------------------------序号
carcode varchar(20) not null,-----------------------------车牌号
cartype varchar(20) not null,-----------------------------品牌车型
dept varchar(40) not null,--------------------------------使用部门
applications varchar(50) not null,------------------------用途
onemonth float not null,----------------------------------一月里程
twomonth float not null,----------------------------------二月里程
threemonth float not null,--------------------------------三月里程
fourmonth float not null,---------------------------------四月里程
fivemonth float not null,---------------------------------五月里程
sixmonth float not null,----------------------------------六月里程
sevenmonth float not null,--------------------------------七月里程
eightmonth float not null,--------------------------------八月里程
ninemonth float not null,---------------------------------九月里程
tenmonth float not null,----------------------------------十月里程
elevenmonth float not null,-------------------------------十一月里程
twelvemonth float not null,-------------------------------十二月里程
total float not null---------------------------------------1到12月的里程合计
)
-----------------------------------往#temp临时表中添加数据---------------------------------
set @sql='insert into #temp(carcode,cartype,dept,applications,onemonth,twomonth,threemonth,fourmonth,fivemonth,sixmonth,sevenmonth,eightmonth,ninemonth,tenmonth,elevenmonth,twelvemonth,total)
select distinct VehicleInfos.VehicleSize,
VehicleBrands.BrandName,
Depts.DeptName,
(case VehicleInfos.VehicleUse
when 0 then ''公务''
when 1 then ''送货''
when 2 then ''烟叶''
when 3 then ''专卖''
when 4 then ''客服''
when 5 then ''安保''
when 6 then ''基建''
end),
max(case when month(CopyDate)=1 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "一月",
max(case when month(CopyDate)=2 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "二月",
max(case when month(CopyDate)=3 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "三月",
max(case when month(CopyDate)=4 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "四月",
max(case when month(CopyDate)=5 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "五月",
max(case when month(CopyDate)=6 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "六月",
max(case when month(CopyDate)=7 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "七月",
max(case when month(CopyDate)=8 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "八月",
max(case when month(CopyDate)=9 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "九月",
max(case when month(CopyDate)=10 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "十月",
max(case when month(CopyDate)=11 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "十一月",
max(case when month(CopyDate)=12 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "十二月",
sum(cast(VehicleMileages.Mileage as decimal(12,2))) as "合计"
from VehicleMileages,VehicleInfos,VehicleBrands,Depts
where VehicleMileages.VehicleInfoID=VehicleInfos.ID and VehicleInfos.VehicleBrandID=VehicleBrands.ID
and VehicleInfos.DeptID=Depts.ID and year(CopyDate)='+@yeartime+'
group by VehicleInfos.VehicleSize,VehicleBrands.BrandName,Depts.DeptName,VehicleInfos.VehicleUse'
exec(@sql)
set @sqlpage='select top '+ltrim(@pagesize)+' id,carcode,cartype,dept,applications,onemonth,twomonth,threemonth,fourmonth,fivemonth,sixmonth,sevenmonth,eightmonth,ninemonth,tenmonth,elevenmonth,twelvemonth,total
from #temp
where #temp.id not in(select top ('+ltrim(@pagecount)+'-1)* '+ltrim(@pagesize)+') #temp.id from #temp) order by #temp.id'
exec(@sqlpage)
end
exec sp_yearlichengReport '2009',10,1
报错:
(119 行受影响)
消息 102,级别 15,状态 1,第 3 行
'10' 附近有语法错误。
select top (('+ltrim(@pagecount)+'-1)*
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_yearlichengReport]
@yeartime varchar(10),------------------------------------------时间参数
@pagesize int,--------------------------------------------------分页中每页显示的信息条数
@pagecount int--------------------------------------------------分页中当前页数
as
begin
declare @sql varchar(8000)-------------------------------------查询的操作语句
declare @sqlpage varchar(8000)-------------------------------------------查询分页的操作语句
create table #temp
(
id int identity(1,1) not null,----------------------------序号
carcode varchar(20) not null,-----------------------------车牌号
cartype varchar(20) not null,-----------------------------品牌车型
dept varchar(40) not null,--------------------------------使用部门
applications varchar(50) not null,------------------------用途
onemonth float not null,----------------------------------一月里程
twomonth float not null,----------------------------------二月里程
threemonth float not null,--------------------------------三月里程
fourmonth float not null,---------------------------------四月里程
fivemonth float not null,---------------------------------五月里程
sixmonth float not null,----------------------------------六月里程
sevenmonth float not null,--------------------------------七月里程
eightmonth float not null,--------------------------------八月里程
ninemonth float not null,---------------------------------九月里程
tenmonth float not null,----------------------------------十月里程
elevenmonth float not null,-------------------------------十一月里程
twelvemonth float not null,-------------------------------十二月里程
total float not null---------------------------------------1到12月的里程合计
)
-----------------------------------往#temp临时表中添加数据---------------------------------
set @sql='insert into #temp(carcode,cartype,dept,applications,onemonth,twomonth,threemonth,fourmonth,fivemonth,sixmonth,sevenmonth,eightmonth,ninemonth,tenmonth,elevenmonth,twelvemonth,total)
select distinct VehicleInfos.VehicleSize,
VehicleBrands.BrandName,
Depts.DeptName,
(case VehicleInfos.VehicleUse
when 0 then ''公务''
when 1 then ''送货''
when 2 then ''烟叶''
when 3 then ''专卖''
when 4 then ''客服''
when 5 then ''安保''
when 6 then ''基建''
end),
max(case when month(CopyDate)=1 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "一月",
max(case when month(CopyDate)=2 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "二月",
max(case when month(CopyDate)=3 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "三月",
max(case when month(CopyDate)=4 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "四月",
max(case when month(CopyDate)=5 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "五月",
max(case when month(CopyDate)=6 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "六月",
max(case when month(CopyDate)=7 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "七月",
max(case when month(CopyDate)=8 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "八月",
max(case when month(CopyDate)=9 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "九月",
max(case when month(CopyDate)=10 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "十月",
max(case when month(CopyDate)=11 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "十一月",
max(case when month(CopyDate)=12 then cast(VehicleMileages.Mileage as decimal(12,2)) else 0 end)as "十二月",
sum(cast(VehicleMileages.Mileage as decimal(12,2))) as "合计"
from VehicleMileages,VehicleInfos,VehicleBrands,Depts
where VehicleMileages.VehicleInfoID=VehicleInfos.ID and VehicleInfos.VehicleBrandID=VehicleBrands.ID
and VehicleInfos.DeptID=Depts.ID and year(CopyDate)='+@yeartime+'
group by VehicleInfos.VehicleSize,VehicleBrands.BrandName,Depts.DeptName,VehicleInfos.VehicleUse'
exec(@sql)
set @sqlpage='select top '+ltrim(@pagesize)+' id,carcode,cartype,dept,applications,onemonth,twomonth,threemonth,fourmonth,fivemonth,sixmonth,sevenmonth,eightmonth,ninemonth,tenmonth,elevenmonth,twelvemonth,total
from #temp
where #temp.id not in(select top ('+ltrim(@pagecount)+'-1)* '+ltrim(@pagesize)+') #temp.id from #temp) order by #temp.id'
print(@sqlpage) --改为打印出来,看一下语句
end
exec sp_yearlichengReport '2009',10,1