/****** Object: StoredProcedure [dbo].[ Pro_pub_fenye] Script date:08/04/2014 11:14:22 ******/set ansi_nulls ongoset quoted_identifier ONGO/*-- suchao--2014-04-27--find public methods */create PROCEDURE [dbo]. [Pro_pub_fenye] @AsyncSql NVARCHAR (MAX), @PageIndex int, @PageSize int, @RecordCount int outputasbeginset NOCOUNT ondeclare @Sql NVARCHAR ( MAX)--Define SQL statement set @[email protected]; DECLARE @pagesql NVARCHAR (MAX) SET @pagesql = ' SELECT * FROM (' [email protected]+ ') as temp WHERE myrank between ' + CAST ( (@PageIndex-1) * @PageSize +1 as NVARCHAR) + ' and ' +cast (@PageIndex * @PageSize as NVARCHAR) + ' EXEC sp_executesql @pagesql-The Execute SQL statement returns the current page data declare @recount NVARCHAR (MAX) SET @recount = ' SELECT @RecordCount =count (1) from (' [email protected]+ ') as temp ' EXEC sp_executesql @recount, N ' @RecordCount int output ', @RecordCount output--Returns the number of affected rows print @ Recordcountreturn @ @errorENDGO
Paging SQL stored procedure algorithm