2012-07-08 回答
不要用斜杠
我看看我这个。没问题
procedure pro_selectbidunit_nowtime_all(in p_id varchar(64),
in p_ifid varchar(64),
in p_userid varchar(64),
in begintime datetime,
in endtime datetime
)
begin
declare v_sql varchar(4000);
declare lev int;
set lev = 1;
if p_userid != ''
then
drop table if exists tmp3;
create table tmp3(
ifid varchar(40),
ifname varchar(50),
ins_ifid varchar(40),
levv int
);
insert tmp3
select ifid
, ifname
, ins_ifid
, 1
from
institutionalframework
where
ins_ifid = p_ifid;
while row_count() > 0
do
set lev = lev + 1;
insert tmp3
select t.ifid
, t.ifname
, t.ins_ifid
, lev
from
institutionalframework t
inner join tmp3 a
on t.ins_ifid = a.ifid and levv = lev - 1;
end while;
insert tmp3
select ifid
, ifname
, ins_ifid
, 0
from
institutionalframework
where
ifid = p_ifid;
end if;
set @v_sql = concat('
select a.id
, a.bidtotalprices
, a.booltender
, bidinvitingunit
, itemname
, bidinvitingnumber
, itemratifyunit
, ratifynumber
, bidinvitinguser
, bidinvitingphone
, starttime
, itemstype
, entrustamount
, entrustdate
, noticetimeone
, noticetimetwo
, noticetimethree
, sellbidfiletime
, itempart
, recorduser
, recordtime
, bidstart
, a.memo
, flowstart
, b.ifid
, b.ifname
, c.id as dicid
, c.itemtext
, principaluser
, principalphone
, principalemail
,synchronflag
from
tenders_bidinvitinguni a
inner join institutionalframework b
on a.itempart = b.ifid
inner join tenders_dictionaryitem c
on c.id = a.itemstype
where a.bidstart = 1 ');
if begintime != ''
then
set @v_sql = concat(@v_sql, ' and a.starttime between ');
set @v_sql = concat(@v_sql, ' date(''');
set @v_sql = concat(@v_sql, begintime);
set @v_sql = concat(@v_sql, ''')');
set @v_sql = concat(@v_sql, ' and date(''');
set @v_sql = concat(@v_sql, endtime);
set @v_sql = concat(@v_sql, ''')');
else
set @v_sql = concat(@v_sql, ' a.starttime between curdate() and date_add(curdate(), interval 0 day) ');
end if;
if p_id != ''
then
set @v_sql = concat(@v_sql, ' and a.id = ');
set @v_sql = concat(@v_sql, p_id);
set @v_sql = concat(@v_sql, '');
end if;
if p_userid != ''
then
set @v_sql = concat(@v_sql, ' and exists (select 1
from
tmp3 f
where
f.ifid = b.ifid)');
end if;
set @v_sql = concat(@v_sql, ' order by a.recordtime asc');
prepare s1 from @v_sql;
execute s1;
deallocate prepare s1;
end