搜索存储过程
create proc test1
@sale_name varchar(50),
@quyu varchar(50),
@subxingzhi_name varchar(50),
@developer_name varchar(50),
@room_type int,
@chaoxiang varchar(50),
@area float,
@price float,
@sql varchar(8000) output
as
set @sql= 'select distinct * from sale
a,householdmodel b,developers c ,subxingzhi d
where a.sale_id=b.sale_id and a.developer_id=c.developer_id and a.subxingzhi_id=d.subxingzhi_id '
if @sale_name <> '1 '
begin
set @sql=@sql+ ' and a.sale_name= ' ' '+@sale_name+ ' ' ' '
end
if @quyu <> '1 '
begin
set @sql=@sql+ ' and a.quyu= ' ' '+@quyu+ ' ' ' '
end
if @subxingzhi_name <> '1 '
begin
set @sql=@sql+ ' and d.subxingzhi_name= ' ' '+@subxingzhi_name+ ' ' ' '
end
if @developer_name <> '1 '
begin
set @sql=@sql+ ' and c.developer_name= ' ' '+@developer_name+ ' ' ' '
end
if @room_type <> '1 '
begin
set @sql=@sql+ ' and b.room_type= '+ '@room_type '
end
if @chaoxiang <> '1 '
begin
set @sql=@sql+ ' and b.chaoxiang= ' ' '+@chaoxiang+ ' ' ' '
end
if @area <> '1 '
begin
set @sql=@sql+ ' and a.area= '+ '@area '
end
if @price <> '1 '
begin
set @sql=@sql+ ' and a.price= '+ '@price '
end
print @sql
exec (@sql)
当我执行的时候提示:
exec test1 '2 ', '2 ', '2 ', '2 ', '2 ', '2 ', '2 ', '2 ',2
提示错误：
select distinct * from sale
a,householdmodel b,developers c ,subxingzhi d
where a.sale_id=b.sale_id and a.developer_id=c.developer_id and a.subxingzhi_id=d.subxingzhi_id and a.sale_name= '2 ' and a.quyu= '2 ' and d.subxingzhi_name= '2 ' and c.developer_name= '2 ' and b.room_type=@room_type and b.chaoxiang= '2 ' and a.area=@area and a.price=@price
服务器: 消息 137，级别 15，状态 2，行 3
必须声明变量 '@room_type '。
[解决办法]
修改
create proc test1
@sale_name varchar(50),
@quyu varchar(50),
@subxingzhi_name varchar(50),
@developer_name varchar(50),
@room_type int,
@chaoxiang varchar(50),
@area float,
@price float,
@sql varchar(8000) output
as
set @sql= 'select distinct * from sale
a,householdmodel b,developers c ,subxingzhi d
where a.sale_id=b.sale_id and a.developer_id=c.developer_id and a.subxingzhi_id=d.subxingzhi_id '
if @sale_name <> '1 '
begin
set @sql=@sql+ ' and a.sale_name= ' ' '+@sale_name+ ' ' ' '
end
if @quyu <> '1 '
begin
set @sql=@sql+ ' and a.quyu= ' ' '+@quyu+ ' ' ' '
end
if @subxingzhi_name <> '1 '
begin
set @sql=@sql+ ' and d.subxingzhi_name= ' ' '+@subxingzhi_name+ ' ' ' '
end
if @developer_name <> '1 '
begin
set @sql=@sql+ ' and c.developer_name= ' ' '+@developer_name+ ' ' ' '
end
if @room_type <> '1 '
begin
set @sql=@sql+ ' and b.room_type= '+@room_type --這裡
end
if @chaoxiang <> '1 '
begin
set @sql=@sql+ ' and b.chaoxiang= ' ' '+@chaoxiang+ ' ' ' '
end
if @area <> '1 '
begin
set @sql=@sql+ ' and a.area= '+@area --這裡
end
if @price <> '1 '
begin
set @sql=@sql+ ' and a.price= '+@price --這裡
end
print @sql
exec (@sql)
