sql2008存储过程多条件拼接语句(转)

create proc select_pictur --根据类别、品名、条码、货号、平方克重、组合or单品查询产品及图片 --查询条件必须有一项 --于2010-4-30制作修改 ( @kp_item_clsno char(6), @kp_item_name varchar(50), @kp_item_henlyno varchar(20), @kp_henly_code varchar(20), @wperk float(8), @item_type char(1) ) as begin declare @sql varchar(500) set @sql='' ---类别 if @kp_item_clsno <>'' begin set @sql=@sql+' and sp.kp_item_clsno='+''''+@kp_item_clsno+'''' end ---品名 if @kp_item_name <>'' begin set @sql=@sql+' and (sp.kp_item_name LIKE '''+'%'+''+@kp_item_name+''+'%'')' end ---条码 if @kp_item_henlyno<>'' begin set @sql=@sql+' and sp.kp_item_henlyno='+''''+@kp_item_henlyno+'''' end ---货号 if @kp_henly_code <>'' begin set @sql=@sql+' and (sp.kp_henly_code LIKE '''+'%'+''+@kp_henly_code+''+'%'')' end ---平方克重 if @wperk<>'' begin set @sql=@sql+' and sp.wperk='+''''+@wperk+'''' end ---组合OR单品 if @item_type<>'' begin set @sql=@sql+' and sp.kp_combine_sta='+''''+@item_type+'''' end exec('select * from jb_spzl sp where 1=1 '+@sql) end