SQL Server Stored Procedure Implementation page

data paging is a common problem during the development of both web and winform Programs , if you implement this process in Code , the efficiency of the server is obviously reduced, you can use the following stored procedures.

Create procedure pagination <br/> @ tblname varchar (255), -- table name <br/> @ strgetfields varchar (1000) = '*', -- columns to be returned <br/> @ fldname varchar (255) = '', -- Name of the sorted field <br/> @ pagesize Int = 10, -- page size <br/> @ pageindex Int = 1, -- page number <br/> @ docount bit = 0, -- total number of returned records, if the value is not 0, <br/> @ ordertype bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending. <br/> @ strwhere varchar (1500) = ''-- Query condition (Note: Do not add where) <br/> as <br/> declare @ strsql varchar (50 00) -- subject sentence <br/> declare @ strtmp varchar (110) -- temporary variable <br/> declare @ strorder varchar (400) -- sorting type <br/> If @ docount! = 0 <br/> begin <br/> If @ strwhere! = ''<Br/> set @ strsql = 'select count (*) as total from ['+ @ tblname +'] where' + @ strwhere <br/> else <br/> set @ strsql = 'select count (*) as total from ['+ @ tblname +'] '<br/> end <br/> -- the above Code indicates that if @ docount is not 0, statistics on the total number of executions. All the following codes are 0 @ docount <br/> else <br/> begin <br/> If @ ordertype! = 0 <br/> begin <br/> set @ strtmp = '<(select min' <br/> set @ strorder = 'order by [' + @ fldname + '] desc '<br/> -- If @ ordertype is not 0, this sentence is very important! <Br/> end <br/> else <br/> begin <br/> set @ strtmp = '> (select Max' <br/> set @ strorder = 'order ['+ @ fldname +'] ASC '<br/> end <br/> If @ pageindex = 1 <br/> begin <br/> If @ strwhere! = ''<Br/> set @ strsql = 'select top' + STR (@ pagesize) + ''+ @ strgetfields + 'from ['+ @ tblname +'] where' + @ strwhere +'' + @ strorder <br/> else <br/> set @ strsql = 'select top' + STR (@ pagesize) + ''+ @ strgetfields + 'from [' + @ tblname + ']' + @ strorder <br/> -- execute the above Code on the first page, this will speed up the execution <br/> end <br/> else <br/> begin <br/> -- the following code gives @ strsql the SQL code to be actually executed <br/> set @ strsql = 'select top' + STR (@ pagesize) + ''+ @ Strgetfields + 'from [' <br/> + @ tblname + '] Where [' + @ fldname + ']' + @ strtmp + '([' + @ fldname + ']) from (select top '+ STR (@ PageIndex-1) * @ pagesize) + '[' + @ fldname + '] from [' + @ tblname + ']' + @ strorder + ') as tbltmp) '+ @ strorder <br/> If @ strwhere! = ''<Br/> set @ strsql = 'select top' + STR (@ pagesize) + ''+ @ strgetfields + 'from [' <br/> + @ tblname + '] Where [' + @ fldname + ']' + @ strtmp + '([' <br/> + @ fldname + ']) from (select top '+ STR (@ PageIndex-1) * @ pagesize) + '[' <br/> + @ fldname + '] from [' + @ tblname + '] where' + @ strwhere + ''<br/> + @ strorder +') as tbltmp) and '+ @ strwhere + ''+ @ strorder <br/> end <br/> exec (@ strsql) <br/> go

 

You only need to specify the table name, sorting column, and other information.