Multi-table Common SQL stored procedure paging
Use [Emailcenter]GO/** * * * object:storedprocedure [dbo]. [Common_pagelist] Script DATE:2016/2/29 11:00:19 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE PROC [dbo].[common_pagelist](@tab nvarchar( $),---table name@strFld nvarchar(Max),--Field String@strWhere varchar(Max),--Where Condition@PageIndex int,--Page Number@PageSize int,--number of records to hold per page@Sort VARCHAR(255),--sort fields and rules without adding an order by@Total intoutput) asDeclare @strSql nvarchar(Max)SetNocount on;Set @strSql='SELECT * FROM (select Row_number () over (ORDER by' + @Sort + ') as RowNum,' + @strFld + ' from' + @tab + 'where 1=1' + @strWhere + ') as Dwhere WHERE rownum between' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar( -))+ ' and' + cast((@PageIndex*@PageSize) as nvarchar( -)) exec(@strSql) Set @strSql='SELECT @Total = COUNT (0) from' + @tab + 'WHERE 1=1' + @strWhere EXECUTEsp_executesql@strSqlN'@Total INT OUTPUT',@TotalOUTPUTSetNocountoff;GO
SQL stored procedure Paging (Common stitching SQL statement idea implementation)