说明,没有加入这条语句前,存储过程正常使用,加入后出现问题就如标题所示
--@se_brand 这是接受的参数
--brand和family是两个表((select f.f_name,b.b_name from family f,brand b where f.f_id=(select f_id from family where f_id=p_f_id) or b.b_id=(select f_b_id from family where f_id=p_f_id))=@se_brand) or
期待大家帮忙看看!谢谢
--@se_brand 这是接受的参数
--brand和family是两个表((select f.f_name,b.b_name from family f,brand b where f.f_id=(select f_id from family where f_id=p_f_id) or b.b_id=(select f_b_id from family where f_id=p_f_id))=@se_brand) or
期待大家帮忙看看!谢谢
--加top 1
select f.f_name,b.b_name
from family f,brand b
where f.f_id=(select top 1 f_id from family where f_id=p_f_id) or
b.b_id=(select top 1 f_b_id from family where f_id=p_f_id))=@se_brand)
或者select top 1
((select f.f_name,b.b_name
from family f,brand b
where f.f_id in (select f_id from family where f_id=p_f_id) or
b.b_id in (select f_b_id from family where f_id=p_f_id))=@se_brand) or
CREATE proc dbo.p_search_disp_height
@p_str nvarchar(200),
@se_brand nvarchar(200),
@se_style int,
@se_mat int,
@se_fun int,
@se_yanse int,
@se_price varchar(100),
@long_d int,
@long_h int,
@width_d int,
@width_h int,
@height_d int,
@height_h int
as
declare @max_money int,@min_money int,@index int
set @index = charIndex('-',@se_price,0)
if @index>0 and @se_price<>'0'
begin
set @min_money =cast(substring(@se_price,1,@index-1) as int)
set @max_money =cast(substring(@se_price,@index+1,len(@se_price)) as int)
end
else
begin
set @min_money = 20000
set @max_money = 2147483647
end select
p_id,
p_ear, ---产品特征
dbo.fn_get_p_code(p_id) as p_code, ---产品编号
p_referprice, ---市场价
dbo.get_price_num(p_id) as p_price, ---实价
p_name,
case p_ispromotions when 1 then '促销' else '会员' end as p_type, ---产品的会员或促销的类型
(select img_filename from imgforproduct where img_p_id=p_id) as img_filename, ---图片名称
p_name as p_art ---产品的全名
from product where
p_inventory>0 and ---库存大于0
p_isadded=1 and ---上架产品
p_isgift=0 and ---非赠品
p_state=1 and
p_isindex_disp=1 and
(p_long>=@long_d and p_long<=@long_h or @long_d is null and @long_h is null) and
(p_width>=@width_d and p_width<=@width_h or @width_d is null and @width_h is null) and
(p_height>=@height_d and p_height<=@height_h or @height_d is null and @height_h is null) and((select b_name from brand where b_id=(select f_b_id from family where f_id=p_f_id))=@se_brand or @se_brand is null or (select f_name from family where f_id=(select f_id from family where f_id=p_f_id)) =@se_brand) and(((select f_s_id from family where f_id=p_f_id) = @se_style) or @se_style is null) and
(((select f_m_id from family where f_id=p_f_id) = @se_mat) or
((select m_parent from material where m_id in (select f_m_id from family where f_id=p_f_id))= @se_mat) or
@se_mat is null) and
(((select f_color from family where f_id=p_f_id) = @se_yanse) or
((select col_parent from [color] where col_id in (select f_color from family where f_id=p_f_id))= @se_yanse) or
@se_yanse is null) and
(p_fu_id = @se_fun or
((select fu_parent from function_house where fu_id=p_fu_id)= @se_fun) or
((select f.f_name,b.b_name from family f,brand b where f.f_id=(select f_id from family where f_id=p_f_id) or b.b_id=(select f_b_id from family where f_id=p_f_id))=@se_brand) or--问题出现的语句
@se_fun is null ) and
((dbo.get_price_num(p_id)>=@min_money and dbo.get_price_num(p_id)<@max_money) or @se_price is null)
order by p_time desc
GO
(select f.f_name,b.b_name from family f,brand b where f.f_id like(select f_id from family where f_id=p_f_id) or b.b_id like (select f_b_id from family where f_id=p_f_id))
--不能直接=@se_brand
--因为select 中查了f.f_name,b.b_name,两个项未经运算怎么能等于一个变量呢?
se_brand有可能是包含了f.f_name,也有可能包含了b.b_name
如:
f.f_name有值 a
b.b_name有值 b
se_brand 是传进来的参数,有可能是a、b、ab、ba
针对于单个a、b
((select b_name from brand where b_id=(select f_b_id from family where f_id=p_f_id))=@se_brand or @se_brand is null or (select f_name from family where f_id=(select f_id from family where f_id=p_f_id)) =@se_brand) and 这一句已经解决了
剩下的是组合部分,请问如何处理,谢谢