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) outputas
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+''''
endif @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+''''
endif @area<>'1'
begin
set @sql=@sql+' and a.area='+'@area'endif @price<>'1'
begin
set @sql=@sql+' and a.price='+'@price'
endprint @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'。
@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) outputas
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+''''
endif @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+''''
endif @area<>'1'
begin
set @sql=@sql+' and a.area='+@area --這裡endif @price<>'1'
begin
set @sql=@sql+' and a.price='+@price --這裡
endprint @sql
exec (@sql)
@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) outputas
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+''''
endif @subxingzhi_name<>'1'
begin
set @sql=@sql+' and d.subxingzhi_name='''+@subxingzhi_name+''''
endif @developer_name<>'1'
begin
set @sql=@sql+' and c.developer_name='''+@developer_name+''''
endif @room_type<>'1'
begin
set @sql=@sql+' and b.room_type='+@room_type --這裡
endif @chaoxiang<>'1'
begin
set @sql=@sql+' and b.chaoxiang='''+@chaoxiang+''''
endif @area<>'1'
begin
set @sql=@sql+' and a.area='+@area --這裡
endif @price<>'1'
begin
set @sql=@sql+' and a.price='+@price --這裡
endprint @sql
exec (@sql)
GO
服务器: 消息 245,级别 16,状态 1,过程 test1,行 55
将 varchar 值 '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=' 转换为数据类型为 int 的列时发生语法错误。还要麻烦大家帮忙解决以下
begin
set @sql=@sql+' and b.room_type='+Cast(@room_type As varchar)
end你也可以借用rtrim函數這麼轉換if @room_type<>'1'
begin
set @sql=@sql+' and b.room_type='+rtrim(@room_type)
end