General stored procedure: a stored procedure for generating a table Paging

Create proc # autogeneration_query_p
@ Tablename varchar (50)
As
Begin
Declare @ host_name varchar (200)
Declare @ get_date datetime
Declare @ sqlroc varchar (8000)
Declare @ description varchar (4000)
Declare @ rowcount int
Select @ sqlroc = '', @ description = '',
@ Host_name = host_name (), @ get_date = getdate ()
Set @ sqlroc = @ sqlroc + 'if exist' (select 1 from sysobjects where id = object_id (''sp _ '+ @ tablename +' _ query '') and xtype in (n''p') '+ char (10)
Set @ sqlroc = @ sqlroc + space (5) + 'drop proc SP _ '+ @ tablename +' _ query' + char (10)
Set @ sqlroc = @ sqlroc + 'Go'
Set @ description = @ description + '/* + ---------------------------------------- +' + char (10)
Set @ description = @ description + '| process name: SP _' + @ tablename + '_ query' + char (10)
Set @ description = @ description + '| Function Description: obtains the paging Stored Procedure of records in the table' + @ tablename + 'according to the conditions' + char (10)
Set @ description = @ description + '| entry parameter: @ searchcondition, @ orderlist, @ pagesize, @ pageindex' + char (10)
Set @ description = @ description + '| process return: number of returned records' + char (10)
Set @ description = @ description + '| maintenance record: Y/A' + char (10)
Set @ description = @ description + '| Use Case: SP _' + @ tablename + '_ query' + char (10)
Set @ description = @ description + '| site name:' + @ host_name + ''+ char (10)
Set @ description = @ description + '| contact information: zlp321001@hotmail.com' + char (10)
Set @ description = @ description + '| creation date:' + convert (varchar (20), @ get_date, 120) + ''+ char (10)
Set @ description = @ description + '+ ---------------------------------------- + */' + char (10)
Select @ sqlroc = @ sqlroc + char (10) + @ description + 'create proc SP _ '+ @ tablename +' _ query'
Set @ sqlroc = @ sqlroc + char (13) + char (10) + space (4) + '@ searchcondition' + space (20-len ('@ searchcondition ')) + 'varchar (2000), -- Query condition'
Set @ sqlroc = @ sqlroc + char (13) + char (10) + space (4) + '@ orderlist' + space (20-len (' @ orderlist ')) + 'varchar (1000), -- Sort list'
Set @ sqlroc = @ sqlroc + char (13) + char (10) + space (4) + '@ pagesize' + space (20-len ('@ pagesize ')) + 'int = 10, -- size of each page'
Set @ sqlroc = @ sqlroc + char (13) + char (10) + space (4) + '@ pageindex' + space (20-len (' @ pageindex ')) + 'int -- page number to be displayed'
Set @ sqlroc = @ sqlroc + char (10) + 'as' + char (10) + 'begin'
Set @ sqlroc = @ sqlroc + char (10) + space (4) + 'set nocount on'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'Clare @ P1 int'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'Clare @ SQL varchar (8000 )'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'Clare @ pagecount int'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'if isnull (@ searchcondition ,'''') <> ''' and isnull (@ orderlist, ''') = '''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'begin'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'set @ SQL = ''select * from' + @ tablename + 'where' + @ searchcondition + '''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'end'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'if isnull (@ searchcondition, ''') = ''' and isnull (@ orderlist, ''') <> '''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'begin'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'set @ SQL = ''select * from' + @ tablename + 'order by''' + @ orderlist + '''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'end'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'if isnull (@ searchcondition ,'''') <> ''' and isnull (@ orderlist, ''') <> '''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'begin'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'set @ SQL = ''select * from' + @ tablename + 'where' + @ searchcondition + ''order by'' + @ orderlist + '''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'end'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'if isnull (@ searchcondition, ''') = ''' and isnull (@ orderlist, ''') = '''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'begin'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'set @ SQL = ''select * from' + @ tablename + ''''
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'end'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + '-- initialize paging curs'
Set @ sqlroc = @ sqlroc + char (10) + space (8) + 'exec sp_cursoropen '+ char (10)
Set @ sqlroc = @ sqlroc + '@ cursor = @ P1 output,' + char (10)
Set @ sqlroc = @ sqlroc + '@ stmt = @ SQL,' + char (10)
Set @ sqlroc = @ sqlroc + '@ scrolopt = 1,' + char (10)
Set @ sqlroc = @ sqlroc + '@ ccopt = 1,' + char (10)
Set @ sqlroc = @ sqlroc + '@ rowcount = @ pagecount output' + char (10)
Set @ sqlroc = @ sqlroc + '-- calculate the total number of pages' + char (10)
Set @ sqlroc = @ sqlroc + 'if isnull (@ pagesize, 0) <1' + char (10)
Set @ sqlroc = @ sqlroc + 'set @ pagesize = 10' + char (10)
Set @ sqlroc = @ sqlroc + 'set @ pagecount = (@ pagecount + @ PageSize-1)/@ pagesize '+ char (10)
Set @ sqlroc = @ sqlroc + 'if isnull (@ pageindex, 0) <1 or isnull (@ pageindex, 0)> @ pagecount '+ char (10)
Set @ sqlroc = @ sqlroc + 'set @ pageindex = 1' + char (10)
Set @ sqlroc = @ sqlroc + 'else' + char (10)
Set @ sqlroc = @ sqlroc + 'set @ pageindex = (@ PageIndex-1) * @ pagesize + 1' + char (10)
Set @ sqlroc = @ sqlroc + '-- display data on the specified page' + char (10)
Set @ sqlroc = @ sqlroc + 'exec sp_cursorfetch @ P1, 16, @ pageindex, @ pagesize '+ char (10)
Set @ sqlroc = @ sqlroc + '-- disable paging curs' + char (10)
Set @ sqlroc = @ sqlroc + 'exec sp_cursorclose @ p1' + char (10)
Set @ sqlroc = @ sqlroc + space (4) + 'set nocount off'
Set @ sqlroc = @ sqlroc + char (10) + 'end'
Print @ sqlroc + char (10) + 'Go'
End
Go

Create proc # sp_generation_query
@ Tablenames varchar (8000)
As
Begin
Declare @ I int
Declare @ tablename varchar (100)
Set @ I = charindex (',', @ tablenames)
While @ I> 0
Begin
Set @ tablename = left (@ tablenames, @ I-1)
Exec # autogeneration_query_p @ tablename
Set @ tablenames = right (@ tablenames, Len (@ tablenames)-@ I)
Set @ I = charindex (',', @ tablenames)
End
If Len (@ tablenames)> 0
Begin
Exec # autogeneration_query_p @ tablenames
End
End
Go

-- Test
# Sp_generation_query 'T'

Drop proc # sp_generation_query
Drop proc # autogeneration_query_p

-- Result
/*

If exists (select 1 from sysobjects where id = object_id ('SP _ t_query') and xtype in (n'p '))
Drop proc sp_t_query
Go
/* + -------------------------------------- +
| Process name: sp_t_query
| Function Description: obtains the paging Stored Procedure for records of table t according to the condition.
| Entry parameter: @ searchcondition, @ orderlist, @ pagesize, @ pageindex
| Process return: number of returned records
| Maintenance record: Y/
| Use Case: sp_t_query
| Site name: RICHWAY-ZJ
| Contact: zlp321001@hotmail.com
| Creation date: 12:32:03
+ -------------------------------------- + */
Create proc sp_t_query
@ Searchcondition varchar (2000), -- Query Condition
@ Orderlist varchar (1000), -- Sort list
@ Pagesize Int = 10, -- size of each page
@ Pageindex int -- the page number to be displayed
As
Begin
Set nocount on
Declare @ P1 int
Declare @ SQL varchar (8000)
Declare @ pagecount int
If isnull (@ searchcondition, '') <>'' and isnull (@ orderlist, '') =''
Begin
Set @ SQL = 'select * from t where '+ @ searchcondition +''
End
If isnull (@ searchcondition, '') ='' and isnull (@ orderlist, '') <>''
Begin
Set @ SQL = 'select * from t order by' + @ orderlist +''
End
If isnull (@ searchcondition, '') <>'' and isnull (@ orderlist, '') <>''
Begin
Set @ SQL = 'select * from t where '+ @ searchcondition + 'ORDER BY' + @ orderlist +''
End
If isnull (@ searchcondition, '') ='' and isnull (@ orderlist, '') =''
Begin
Set @ SQL = 'select * from t'
End
-- Initialize the paging cursor
Exec sp_cursoropen
@ Cursor = @ P1 output,
@ Stmt = @ SQL,
@ Scrolopt = 1,
@ Ccopt = 1,
@ Rowcount = @ pagecount output
-- Calculate the total number of pages
If isnull (@ pagesize, 0) <1
Set @ pagesize = 10
Set @ pagecount = (@ pagecount + @ PageSize-1)/@ pagesize
If isnull (@ pageindex, 0) <1 or isnull (@ pageindex, 0)> @ pagecount
Set @ pageindex = 1
Else
Set @ pageindex = (@ PageIndex-1) * @ pagesize + 1
-- Display data on a specified page
Exec sp_cursorfetch @ P1, 16, @ pageindex, @ pagesize
-- Disable paging cursor
Exec sp_cursorclose @ p1
Set nocount off
End
Go

*/