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 namestr			VARCHAR(100)
	,in process			TINYINT #boolean 值
	,in companyid   INT
	,in isauditor			TINYINT #boolean 值
	,IN	isapprover		TINYINT #boolean 值
	,in PageIndex	INT
	,in PageSize		INT
	,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 @companyid									WHEN 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

　　

CALL userinfo_page(
0
,''
,''
,0
,10
,@TotalRecords );