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 @[email protected]+ ' and a.sale_name= ' ' '[email protected]_name+ ' ' ' '
end
if @quyu <> '1 '
begin
set @[email protected]+ ' and a.quyu= ' ' '[email protected]+ ' ' ' '
end
if @subxingzhi_name <> '1 '
begin
set @[email protected]+ ' and d.subxingzhi_name= ' ' '[email protected]_name+ ' ' ' '
end
if @developer_name <> '1 '
begin
set @[email protected]+ ' and c.developer_name= ' ' '[email protected]_name+ ' ' ' '
end
if @room_type <> '1 '
begin
set @[email protected]+ ' and b.room_type= '+ '@room_type '
end
if @chaoxiang <> '1 '
begin
set @[email protected]+ ' and b.chaoxiang= ' ' '[email protected]+ ' ' ' '
end
if @area <> '1 '
begin
set @[email protected]+ ' and a.area= '+ '@area '
end
if @price <> '1 '
begin
set @[email protected]+ ' 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 [email protected]_type and b.chaoxiang= '2 ' and [email protected] and [email protected]
服务器: 消息 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