--就是最后一个参数有问题,前面的参数都正确,就是有最后一个就又错了
--错误时:消息 4104,级别 16,状态 1,第 10 行无法绑定由多个部分组成的标识符 "pre.createtime"。
--pre.createtime这个我就不知道哪里来的 没用着啊[proc_query_flight_his] 1,10,'','','北京','上海','2011-12-20','06:00-12:00','CA4569','数据导入','CA','2011-11-11','2011-12-31'ALTER PROCEDURE [dbo].[proc_query_flight_his]
(
@page INT, --当前页
@pagesize INT, --页大小
@orderby VARCHAR(50), --排序字段
@order VARCHAR(50), --排序方式
@begincity VARCHAR(50), --始发地
@endcity VARCHAR(50), --目的地
@date VARCHAR(50), --出发日期
@time VARCHAR(50), --出发时刻
@airno VARCHAR(50), --航班号
@source VARCHAR(50), --数据来源
@airline VARCHAR(50), --航空公司
@updatetime varchar(50), --开始更新时间
@endtime VARCHAR(50) --结束更新时间
)
ASdeclare @sql varchar(4000)
DECLARE @subsql VARCHAR(1000)--保存结果的临时表
if object_id( 'tempdb..#s') is not null drop table #s
create table #s (
[id] [int] identity(1,1),
[flid] [varchar](50) NULL,
[beginairport] [varchar](50) NULL,
[endairport] [varchar](50) NULL,
[airline] [varchar](50) NULL,
[airmodel] [varchar](50) NULL,
[begincity] [varchar](50) NULL,
[endcity] [varchar](50) NULL,
[beginterminal] [varchar](50) NULL,
[endterminal] [varchar](50) NULL,
[sn] [varchar](50) NULL,
[begindate] [datetime] NULL,
[begintime] [datetime] NULL,
[endtime] [datetime] NULL,
[totaltime] [datetime] NULL,
[totallength] [bigint] NULL,
[type] [varchar](10) NULL,
[price] [bigint] NULL,
[discount] [int] NULL,
[faredesc] [varchar](100) NULL,
[re] [varchar](50) NULL,
[flighttype] [smallint] NULL,
[stoptimes] [smallint] NULL,
[timerate] [smallint] NULL,
[oilfee] [bigint] NULL,
[porttax] [bigint] NULL,
[updatetime] [datetime] NULL,
[source] [varchar](50) NULL
)set @subsql = ''
if @begincity is not null and @begincity != ''
set @subsql = @subsql + ' and ct1.city = '''+@begincity+''''
if @endcity is not null and @endcity != ''
set @subsql = @subsql + ' and ct2.city = '''+@endcity+''''
if @date is not null and @date != ''
set @subsql = @subsql + ' and fh.begindate = '''+@date+''''
if @time is not null and @time != ''
BEGIN
DECLARE @start VARCHAR(10)
DECLARE @end VARCHAR(10)
SELECT @start=col FROM dbo.g_split(@time,'-') WHERE id = 1 --得到开始时刻
SELECT @end=col FROM dbo.g_split(@time,'-') WHERE id = 2 --得到结束时刻
SET @subsql = @subsql + ' and LEFT(RIGHT(CONVERT(VARCHAR(16),fh.begintime,108),8),5) >= '''+@start+''''
SET @subsql = @subsql + ' and LEFT(RIGHT(CONVERT(VARCHAR(16),fh.begintime,108),8),5) <= '''+@end+''''
END
if @endtime is not null and @endtime != ''
set @subsql = @subsql + ' and pre.createtime = '''+@endtime+''''
if @airno is not null and @airno != ''
set @subsql = @subsql + ' and fh.sn = '''+@airno+''''
if @source is not null and @source != ''
set @subsql = @subsql + ' and fh.source = '''+@source+''''
if @airline is not null and @airline != ''
set @subsql = @subsql + ' and air.code = '''+@airline+''''
if @updatetime is not null and @updatetime != ''
set @subsql = @subsql + ' and CONVERT(varchar(10),fh.updatetime,120) >= '''+@updatetime+''''
if @endtime is not null and @endtime != ''
set @subsql = @subsql + ' and CONVERT(varchar(10),fh.updatetime,120) >= '''+@endtime+''''SET @sql = 'SELECT fh.flid,(ap1.name+'''+' '+'''+ap1.code) beginairport,(ap2.name+ap2.code) endairport,(air.name+air.code) airline,
am.code airmodel,fh.begincity,fh.endcity,fh.beginterminal,fh.endterminal,fh.sn,fh.begindate,fh.begintime,fh.endtime,
fh.totaltime,fh.totallength,fh.type,fh.price,fh.discount,fh.faredesc,fh.re,fh.flighttype,fh.stoptimes,
fh.timerate,fh.oilfee,fh.porttax,fh.updatetime,fh.source FROM dbo.flight_his fh
INNER JOIN dbo.airline air ON fh.alid = air.alid
INNER JOIN dbo.airmodel am ON am.aoid = fh.airaoid
INNER JOIN dbo.airport ap1 ON ap1.apid = fh.beginapid
INNER JOIN dbo.airport ap2 ON ap2.apid = fh.endapid
INNER JOIN dbo.city ct1 ON ct1.ctid = fh.begincity
INNER JOIN dbo.city ct2 ON ct2.ctid = fh.endcity where 1 = 1 '+@subsql+' 'IF @orderBy IS NOT NULL AND @orderBy != ''
BEGIN
SET @sql += ' order by ' + @orderBy + ' ' + @order
END
insert #s (
[flid],
[beginairport],
[endairport],
[airline],
[airmodel],
[begincity],
[endcity],
[beginterminal],
[endterminal],
[sn],
[begindate],
[begintime],
[endtime],
[totaltime],
[totallength],
[type],
[price],
[discount],
[faredesc],
[re],
[flighttype],
[stoptimes],
[timerate],
[oilfee],
[porttax],
[updatetime],
[source]
)
execute (@sql)exec proc_page '#s','id',0,0,'*',5,@pagesize,@page,1,100,''
return (select count(1) from #s)
解决方案 »
- !!!XP中没有Microsoft Search服务,可以用全文索引功能吗?
- 问个简单的查询
- a 服务器能注册上B服务器,而B服务器注册不上a服务器why???
- 查询包含某个字符的所有记录,请高手指点,谢谢
- 使用 EXISTS 和NOT EXISTS 效率一样吗?
- 有没有什么函数可以统计某字符在指定字符串里出现的次数?
- 酒店预定的数据库设计
- 超高难度:关联的两个表的SQL分页问题。
- 求助!关于mysql服务器不能启动的新问题
- 安装SQL Server 2005后,然后又装了SqlServer2000 然后所有数据库里的 varchar,char类型的字段都显示不了中文,怎么解决啊??
- 求大神鉴定,此人写的这个是脑残误导,还是什么意思
- 表中 某个字段中如何查找特定格式的字符并删除?
set @subsql = @subsql + ' and pre.createtime = '''+@endtime+''''但是你的表别名中没有pre
set @subsql = @subsql + ' and pre.createtime = '''+@endtime+''''
--这里发现了