Create procedure ds_getdatalist
(
@ Tblname varchar (255) = '', -- table name
@ Strgetfields varchar (1000) = '*', -- the column to be returned
@ Fldname varchar (255) = 'releasedate', -- Name of the sorted Field
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Recordcount int output, -- total number of output records
@ Ordertype bit = 1, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (1500) = ''-- Query condition (Note: Do not add where)
)
As
Declare @ strsql varchar (5000) -- subject sentence
Declare @ countsql nvarchar (4000) -- Statement
Declare @ strtmp varchar (1000) -- Temporary Variable
Declare @ strorder varchar (1000) -- sort type
If @ strwhere! =''
Set @ CountSQL = 'select @ RecordCount = count (*) from ['+ @ tblName +'] where' + @ strWhere +''
Else
Set @ CountSQL = 'select @ RecordCount = count (*) from ['+ @ tblName +']'
Exec sp_executesql @ CountSQL, n' @ RecordCount int out', @ RecordCount out
If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'ORDER BY' + @ fldName + 'desc'
End
Else
Begin
Set @ strTmp = '> (select max'
Set @ strOrder = 'ORDER BY' + @ fldName + 'asc'
End
If @ PageIndex = 1
Begin
If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields +' from ['+ @ tblName +'] where ('+ @ strWhere + ') '+ @ strOrder
Else
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields +' from ['+ @ tblName +'] '+ @ strOrder
End
Else
Begin
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields + 'from [' + @ tblName + '] where' + @ fldName +'' + @ strTmp + '(' + @ fldName + ') from (select top '+ str (@ PageIndex-1) * @ PageSize) + ''+ @ fldName +' from ['+ @ tblName +'] '+ @ strOrder + ') as tblTmp) '+ @ strOrder
If @ strWhere! =''
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields + 'from [' + @ tblname + '] Where' + @ fldname +'' + @ strtmp + '(' + @ fldname + ') from (select top '+ STR (@ PageIndex-1) * @ pagesize) + ''+ @ fldname + 'from [' + @ tblname + '] Where (' + @ strwhere + ')' + @ strorder + ') as tbltmp) and '+ @ strwhere + ''+ @ strorder
End
Exec (@ strsql)
Go