搜索存储过程

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)