mysql 存储过程 compile_mysql 存储过程

CREATE PROCEDURE sample_report_page

(

in Nos VARCHAR(100)

,IN suffix VARCHAR(100)

,IN check_category VARCHAR(100)

,IN manufacturer VARCHAR(200)

,in client_name VARCHAR(200)

,in namestrVARCHAR(100)

,in processTINYINT #boolean 值

,in companyid INT

,in isauditorTINYINT #boolean 值

,INisapproverTINYINT #boolean 值

,in PageIndexINT

,in PageSizeINT

,OUT TotalRecords INT

)

begin

set @startRow = PageIndex * PageSize;

set @pageSize = PageSize;

set @Nos=Nos;

set @suffix=suffix;

set @check_category=check_category;

set @manufacturer=manufacturer;

set @client_name=client_name;

set @namestr=namestr;

set @process=process;

set @companyid=companyid;

set @isauditor=isauditor;

set @isapprover=isapprover;

set @strsql = concat(

'select sql_calc_found_rows * from sample_report_view '

,' where '

);

set @strsql=concat(@strsql,CASE IFNULL(@Nos, '') WHEN '' THEN ' Nos=Nos and ' ELSE CONCAT(' Nos=''', @Nos, ''' and ') END);

set @strsql=concat(@strsql,CASE IFNULL(@suffix, '') WHEN '' THEN ' suffix=suffix and' ELSE CONCAT(' suffix=''', @suffix, ''' and ') END);

set @strsql=concat(@strsql,CASE IFNULL(@check_category, '') WHEN '' THEN ' check_category=check_category and ' ELSE CONCAT(' check_category=''', @check_category, ''' and ') END);

set @strsql=concat(@strsql,CASE IFNULL(@manufacturer, '') WHEN '' THEN ' manufacturer=manufacturer and' ELSE CONCAT(' manufacturer=''', @manufacturer, ''' and ') END);

set @strsql=concat(@strsql,CASE IFNULL(@client_name, '') WHEN '' THEN ' client_name=client_name and ' ELSE CONCAT(' client_name=''', @client_name, ''' and ') END);

set @strsql=concat(@strsql,CASE IFNULL(@namestr, '')WHEN '' THEN ' name=name and ' ELSE CONCAT(' name=''', @namestr,''' and ') END);

set @strsql=concat(@strsql,CASE IFNULL(@process, '')WHEN '' THEN ' process=process and ' ELSE CONCAT(' process=',@process,' and ') END);

set @strsql=concat(@strsql,CASE @companyidWHEN 0 THEN ' companyid=companyid ' ELSE CONCAT(' companyid=',@companyid,' ') END);

set @strsql=concat(@strsql,CASE IFNULL(@isauditor,'')WHEN '' THEN ' ' WHEN 1 THEN ' and auditor_date IS NOT NULL ' ELSE ' and auditor_date IS NULL ' END);#时间列 auditor_date=auditor_date 这样是查不到数据的

set @strsql=concat(@strsql,CASE IFNULL(@isapprover,'')WHEN '' THEN ' ' WHEN 1 THEN ' and approver_date IS NOT NULL ' ELSE CONCAT(' and approver_date IS NULL') END);

set @strsql=concat(@strsql,' order by id desc ');

set @strsql=concat(@strsql,' limit ?,?; ');

prepare PageSql from @strsql;

execute PageSql using @startRow, @pageSize;

deallocate prepare PageSql;

set TotalRecords = found_rows();

end