ALTER PROCEDURE dbo.****_SearchHotel
@city varchar(50),
@star varchar(20),
@hotel varchar(100),
@sort varchar(100),
@district varchar(100),
@sowntown varchar(100),
@ePrice float,
@sPrice float
AS
declare @strField varchar(500) -- select
declare @strSQL varchar(1500) -- 主语句
declare @strWhere varchar(500) -- 临时变量
declare @strOrder varchar(500) -- 排序类型
declare @tb table(hotel_id varchar(20), member_price decimal)
/* -- 设置排序条件开始 -- */
if(@sort is not null and @sort <> '')
BEGIN
if(@sort = 'is_extend')
SET @strOrder = ' order by a.is_extend desc '
else if(@sort ='member_price')
SET @strOrder = ' order by b.member_price asc '
else
SET @strOrder = ' order by a.star asc '
END
else
SET @strOrder = ' order by b.member_price asc '
/* --------------------------------- */
/* -- 设置where条件开始 -- */
SET @strWhere = ''
if(@city is not null and @city <> '')
SET @strWhere = ' WHERE a.city = '''+ @city + ''''
if(@district is not null and @district <> '')
BEGIN
IF(@strWhere is not null and @strWhere <> '')
SET @strWhere = ' WHERE a.district = '''+@district + ''''
ELSE
SET @strWhere = ' AND a.district = '''+ @district + ''''
END
else if(@sowntown is not null and @sowntown <> '')
BEGIN
IF(@strWhere is not null and @strWhere <> '')
SET @strWhere = ' WHERE a.sowntown = '''+@sowntown + ''''
ELSE
SET @strWhere = ' AND a.sowntown = '''+@sowntown + ''''
END
if(@sPrice is not null and @sPrice >0 and @ePrice is not null and @ePrice > 0)
BEGIN
IF(@strWhere is not null and @strWhere <> '')
SET @strWhere = ' WHERE b.member_price between '''+@sPrice + ''' and '''+@ePrice+''''
else
SET @strWhere = ' AND b.member_price between '''+@sPrice + ''' and '''+@ePrice+''''
END
if(@hotel is not null and @hotel <> '')
BEGIN
if(@strWhere is not null and @strWhere <> '')
SET @strWhere = ' WHERE a.hotel_name = '''+@hotel+''''
else
SET @strWhere = ' AND a.hotel_name = '''+@hotel +''''
END
/* --------------------------------- */
insert into @tb(hotel_id,member_price) select hotel_id,min(member_price) from hotel_room group by hotel_id order by min(member_price) ASC
SET @strField = 'select a.hotel_name as hotel_name, a.hotel_id as hotel_id, isnull(a.star, '''') as star, isnull(a.sowntown, '''') as sowntown, isnull(a.hotel_traffic, '''') as hotel_traffic, isnull(a.hotel_intro,'''') as hotel_intro, a.is_extend as is_extend,b.member_price as member_price from hotel_detail as a inner join #tb as b on a.hotel_id = b.hotel_id '
SET @strSQL = @strField + @strWhere + @strOrder
exec (@strSQL)
delete @tb
后面试了很多方式都不行,后面想到把表变理换成临时表,作相应的修改:
CREATE TABLE #tb(hotel_id varchar(20), member_price decimal)
然后再把下面的@tb替换成为#tb,问题就解决了,没有再出现错误.
其实在数据量大的情况下还是使用临时表更加合适的.
反思:为什么用临时表不会出错而使用表变量就会出错呢?
