Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: <Yang zhixiang>
-- Create Date: <>
-- Description: <Based on the condition group according to>
-- Exec getpagelistbygroup 'productseries', '*', 3, 5, 'classtree _ id', 'parentclassid ', '7', 'productseries _ id', 'desc ', 'productseriesname like ''% Bo % '''
-- ===================================================== ======
Create procedure getpagelistbygroup
@ Tblname varchar (255), -- table name
@ Fldstr varchar (1000), -- Field List
@ Pagesize int, -- page size
@ Pageindex int, -- page number, page number
@ Group varchar (255), -- group field
@ Parent varchar (255), -- filter Fields
@ Parentstr varchar (1000), -- filter Condition
@ Ordname varchar (255), -- Name of the sorted Field
@ Ordby char (4), -- Sort Method
@ Where varchar (1000) -- Query Condition
As
Begin
Declare @ SQL varchar (8000)
Set @ SQL =''
Set @ SQL = @ SQL + 'select' + @ fldstr + 'from' + @ tblname + 'where' + @ group + 'in ('
Set @ SQL = @ SQL + 'select top ('+ convert (varchar (50), @ pagesize) +') '+ @ group +' from ('
Set @ SQL = @ SQL + 'select top ('+ convert (varchar (50), @ pagesize * @ pageindex) + ') '+ @ group + 'from' + @ tblname + 'where' + @ parent +' = '+ @ parentstr + 'group by' + @ group + ') GRP order by '+ @ group + 'desc'
Set @ SQL = @ SQL + ')'
If Len (@ where)> 0
Set @ SQL = @ SQL + 'and' + @ where
Set @ SQL = @ SQL + 'ORDER BY' + @ group + 'asc'
If Len (@ ordname)> 0
Set @ SQL = @ SQL + ',' + @ ordname + ''+ @ ordby
Execute (@ SQL );
End
Go