Select
hotel.hotel_id As hotelid,
( Case When @code = 'en' Then hotel.hotel_name_en Else hotel.hotel_name End) As hotelname,
hotel.star As star,
( Case When @code = 'en' Then hotel.hotel_address_en Else hotel.hotel_address End) As address,
hotel.phone As phone,
hotel.fax As fax,
( Case When @code = 'en' Then hotel.intro_en Else hotel.intro End) As intro
From
hotel
Where
hotel.hotel_id = @hotelid
End
If @@error !=0
Begin
RollBack
RaisError ('SQL SERVER 2000,xxx_rp_hotel_search 发生错误.', 16, 1) with Log --记录日志
Return -1
End
Return
Grant Execute On dbo.xxx_rp_hotel_search To Public
Go
根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令:set statistics io on, set statistics time on , set showplan on 等。
存储过程在ASP中的调用规范:
'建立连接
set con=Server.CreateObject("ADODB.Connection")
StrConn = "driver={SQL Server};server=192_168_16_148;uid=sa;pwd=mast;database=bdg;"
con.Open StrConn
set cmd=Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection=con
cmd.CommandText="dbo.xxx_rp_hotel_info"
‘参数
ReDim p(1)
p(0)=cstr(HotelId)
p(1)=cstr(Code)
‘存储过程
Set hotel_rs=cmd.execute( ,p)
if hotel_rs.eof then
responseError "Sorry, Cann't Find Info.."
end if