Paging Stored Procedures

Use [db_jp_baseinfo]go/****** object:storedprocedure [dbo].    [Sp_hegeindexreport] Script date:12/07/2015 10:28:21 ******/set ansi_nulls ongoset quoted_identifier Ongo--------------------------------------According to the time period to inquire about the passing rate of registration points--------------------------------------ALTER PROCEDURE [dbo]. [Sp_hegeindexreport] @pageSize int=1,--number of pages @pageindex int=1,--current page @orderby varchar () = ' a.id ',--sort word Segment [email protected] int= ' ASC ', @StartTime datetime,--Sort type @endtime datetime,[email protected] Var CHAR (max)--Query condition as BEGIN Declare @beginRow int, @endRow int, @sql varchar (max) Set @beginRow = @pageSize * (@pageIndex- 1) + 1 Set @endRow = @pageSize * @pageIndexDeclare @mainSql varchar (max), @getCountSql varchar (max) Set @mainSql = ' Select * , Row_number () over (ORDER by ' [email protected]+ ') as ROW from (SELECT aa.*, (case) AA. Kemuyibaomingcount=0 then 0 ELSE aa. Kemuyihegecount/aa. Kemuyibaomingcount END) as Kemuyihegeindex, (case is AA.) Kemuerbaomingcount=0 then 0 ELSE aa. Kemuerhegecount/aa. Kemuerbaomingcount END) as Kemuyiergeindex, (case when AA. Kemusanbaomingcount=0 then 0 ELSE aa. Kemusanhegecount/aa. Kemusanbaomingcount END) as Kemusanhegeindex, (case when AA. Kemusibaomingcount=0 then 0 ELSE aa. Kemusihegecount/aa. Kemusibaomingcount END) as Kemusihegeindexfrom (SELECT Reg_tem. Regsiteno, Reg_tem.        Regsitename, (SELECT count (*) from checkresults WHERE checktype= ' account One ' and @StartTime <checktime and checktime< @EndTime and Stuid in (SELECT stuid from student WHERE Regsitei D=reg_tem.id and Isdelete=0)) as Kemuyibaomingcount, (SELECT count (*) from Checkresults WHERE Ch Ecktype= ' and @StartTime <checktime and checktime< @EndTime and Checkresult=1 and St      UId in    (SELECT stuid from student WHERE regsiteid=reg_tem.id and isdelete=0)) As Kemuyihegecount, (SELECT count (*) from checkresults WHERE checktype= ' account One ' and @StartTime &LT;CHEC Ktime and checktime< @EndTime and checkresult=2 and Stuid in (SELECT stuid from      Student WHERE Regsiteid=reg_tem.id and isdelete=0) as Kemuyiquekaocount, (SELECT count (*)        From Checkresults WHERE checktype= ' account two ' and @StartTime <checktime and checktime< @EndTime and Stuid in (SELECT stuid from student WHERE Regsiteid=reg_tem.id and isdelete=0 ) as Kemuerbaomingcount, (SELECT count (*) from checkresults WHERE checktype= ' account two ' and @StartTime &L T Checktime and checktime< @EndTime and Checkresult=1 and Stuid in (SELECT stuid F ROM Student WHERE Regsiteid=reg_tem.id and Isdelete=0)) as Kemuerhegecount, (SELECT count (*) from Checkresults WHERE checkt Ype= ' account two ' and @StartTime <checktime and checktime< @EndTime and checkresult=2 and Stuid In (SELECT stuid from student WHERE regsiteid=reg_tem.id and isdelete=0) as Kemu         Erquekaocount, (SELECT count (*) from checkresults WHERE checktype= ' account three ' and @StartTime <checktime and checktime< @EndTime and Stuid in (SELECT stuid from student WHERE regsit  Eid=reg_tem.id and Isdelete=0)) as Kemusanbaomingcount, (SELECT count (*) from Checkresults WHERE  Checktype= ' subject three ' and @StartTime <checktime and checktime< @EndTime and Checkresult=1 and Stuid in (SELECT stuid from student WHERE regsiteid=reg_tem.id and isdelete=0))    As Kemusanhegecount, (SELECT count (*) from checkresults WHERE checktype= ' account three ' and @StartTime <checktime and Checkt  ime< @EndTime and checkresult=2 and Stuid in (SELECT stuid from student WHERE      Regsiteid=reg_tem.id and Isdelete=0) as Kemusanquekaocount, (SELECT count (*) from Checkresults          WHERE checktype= ' account Four ' and @StartTime <checktime and checktime< @EndTime and Stuid in (SELECT stuid from student WHERE regsiteid=reg_tem.id and isdelete=0)) As Kemusibaomingcount, (SELECT count (*) from checkresults WHERE checktype= ' account Four ' and @StartTime <c Hecktime and checktime< @EndTime and Checkresult=1 and Stuid in (SELECT stuid FR      OM student WHERE Regsiteid=reg_tem.id and isdelete=0) as Kemusihegecount, (SELECT count (*) From Checkresults WHERE checktype= ' sectionMesh Four "and @StartTime <checktime and checktime< @EndTime and checkresult=2 and Stuid in (SELECT stuid from student WHERE regsiteid=reg_tem.id and isdelete=0)) As Kemusiquekaocount from Regsite reg_tem WHERE Reg_tem. isdelete=0) AA) A ' [Email protected]set @sql = ' Select * FROM (' [email protected]+ ') twhere t.row between ' +con Vert (nvarchar (9), @beginRow) + ' and ' +convert (nvarchar (9), @endRow) Set @getCountSql = ' Select Count (*) from (' [email     protected]+ ') Tb '--Gets the total number of execute (@sql) execute (@getCountSql) END based on the condition

　　

Paging Stored Procedures