Asp.net SQL stored procedure paging code

Use [data_smf]
Go
/***** Object: storedprocedure [dbo]. [catsearch] script Date: 01/23/2011 04:34:30 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: <author, name>
-- Create date: <create date,>
-- Description: <description,>
-- ===================================================== ======
Create procedure [dbo]. [catsearch]

@ Perfectkey nvarchar (20) = 'close', -- whether it matches exactly
@ Pagecout int = 1, -- returns the first page and the total number of pages when the total number of pages or data is equal to 0 and the total number of pages returned is equal to-1.
@ Searchvalue nvarchar (50) = '-', -- the content to be searched
@ Searchcolumn nvarchar (50) = '-', -- Name of the queried Column
@ Country nvarchar (50) = '-', -- Region

@ Numberfew int =-1,
@ Numberrentint =-1,
@ Pricelow int =-1,
@ Pricehigh int =-1,
@ Days int = 3,

@ Ordercolmn nvarchar (50) = 'puttime', -- the field used for sorting
@ Order nvarchar (50) = 'desc ',
@ Size int = 20, -- the number of data entries per page
@ Column nvarchar (100) = '*', -- returned Field
@ Table1 nvarchar (100) = 'produce ', -- Name of the table to be queried
@ Table2 nvarchar (100) = 'produce4', -- Name of the table to be queried
@ Table3 nvarchar (100) = '-' -- Name of the table to be queried
As
Begin
Set nocount on;
Declare @ shortcache nvarchar (1000)
Declare @ selectstr nvarchar (1000)
Declare @ sqlpagecountstr nvarchar (3000)
Declare @ sqlstr nvarchar (3000)
Declare @ sqlstr1 nvarchar (1000)
Declare @ sqlstr2 nvarchar (1000)
Declare @ sqlstr3 nvarchar (1000)
Declare @ ifnull int
Set @ ifnull = 0
Set @ selectstr = 'where'
Set @ shortcache =''

 

------------- Quantity [count] is the column name used for sorting
------------- Used to query the number of items to generate a condition connected by and. Skipped when no condition is entered.
---- Complete
If (@ numberfew <>-1 and @ numberw <>-1)
Begin
Set @ shortcache = '([counts] between' + cast (@ numberfew as nvarchar (50) + 'and' + cast (@ numberbench as nvarchar (50) + ') and'
End
Else if (@ numberfew <>-1 and @ numberw =-1)
Begin
Set @ shortcache = '([counts] =>' + cast (@ numberfew as nvarchar (50) + ') and'
End
Else if (@ numberfew =-1 and @ numberw <>-1)
Begin
Set @ shortcache = '([counts] <=' + cast (@ numberbench as nvarchar (50) + ') and'
End
If (@ shortcache <> '')
Begin
Set @ selectstr = @ selectstr + @ shortcache
Set @ shortcache =''
End
Else
Set @ ifnull = @ ifnull + 1

---- Price [price] price is the column name used for sorting
---- Used to limit the price of a product connected by and. Skipped when no value is entered
---- Complete
If (@ pricelow <>-1 and @ pricehigh <>-1)
Begin
Set @ shortcache = '([price] between' + cast (@ pricelow as nvarchar (50) + 'and' + cast (@ pricehigh as nvarchar (50) + ') and'
End
If (@ pricelow <>-1 and @ pricehigh =-1)
Begin
Set @ shortcache = '(cast ([price] as int)>' + cast (@ pricelow as nvarchar (50) + ') and'
End
If (@ pricelow =-1 and @ pricehigh <>-1)
Begin
Set @ shortcache = '(cast ([price] as int) <' + cast (@ pricehigh as nvarchar (50) + ') and'
End
If (@ shortcache <> '')
Begin
Set @ selectstr = @ selectstr + @ shortcache
Set @ shortcache =''
End
Else
Set @ ifnull = @ ifnull + 1

-------- Time [puttime] is the column name used for sorting
If (@ days <>-1)
Begin
Set @ shortcache = '(cast (datediff (dd, [puttime], getdate () as int) between-1 and' + convert (nvarchar (50), @ days) + ') and'
Set @ selectstr = @ selectstr + @ shortcache
End
Else
Set @ ifnull = @ ifnull + 1

-------- Address
If (@ country <> '-')
Begin
Set @ shortcache = '(country = ''' + @ country + ''') and'
Set @ selectstr = @ selectstr + @ shortcache
End
Else
Set @ ifnull = @ ifnull + 1

------ Exact match: [name] [text] [trader] is the column name used for sorting.
If (@ perfectkey = 'open' and @ searchvalue <> '-')
Begin
If (@ searchcolumn = '-')
Begin
Set @ shortcache = '([name] = ''' + @ searchvalue + ''' or' + '[text] = ''' + @ searchvalue + ''' or' + '[trader] = ''' + @ searchvalue + ''') and'
---------- Set @ shortcache = 'where [name] = '000000' or [text] = '000000' or [price] = '000000 '''
Set @ selectstr = @ selectstr + @ shortcache
End
Else
Begin
Set @ shortcache = '([' + @ searchcolumn + '] = ''' + @ searchvalue + ''') and'
---------- Set @ shortcache = 'where [name] = ''potato '''
Set @ selectstr = @ selectstr + @ shortcache
End
End

 

-------- Incomplete match Detected
Else if (@ perfectkey = 'close' and @ searchvalue <> '-')
Begin
If (@ searchcolumn = '-')
Begin
Set @ shortcache = '([name] like ''%' + @ searchvalue + '%'' or [text] like ''%' + @ searchvalue + '%'' or [trader] like ''% '+ @ searchvalue +' % '') and'
---------- Set @ shortcache = '1 = 1'
Set @ selectstr = @ selectstr + @ shortcache
End
Else
Begin
Set @ shortcache = '(' + @ searchcolumn + 'like ''%' + @ searchvalue + '%'') and'
Set @ selectstr = @ selectstr + @ shortcache
End
End
Else
Begin
Set @ ifnull = @ ifnull + 1
End
If (@ ifnull = 5)
Begin
Set @ selectstr =''
End
Else
Begin
Set @ selectstr = @ selectstr + '1 = 1'
End

--------- Merge query statement
Set @ sqlstr1 = 'select top 200 '+ @ column + 'from' + @ table1 + ''+ @ selectstr +''
If (@ table2 <> '-')
Begin
Set @ sqlstr2 = 'select top 200 '+ @ column + 'from' + @ table2 + ''+ @ selectstr +''
End
Else
Begin
Set @ sqlstr2 =''
End

If (@ table3 <> '-')
Begin
Set @ sqlstr3 = 'select top 200 '+ @ column + 'from' + @ table3 + ''+ @ selectstr +''
End
Else
Begin
Set @ sqlstr3 =''
End
Set @ sqlstr = 'select * from (select top 1000 row_number () over (order by '+ @ ordercolmn + ''+ @ order +') as rownum, * from ('+ @ sqlstr1 + 'Union all' + @ sqlstr2 +') s) t where t. rownum between '+ cast (@ size as nvarchar (20) +' * ('+ cast (@ pagecout as nvarchar (20) +'-1) and '+ cast (@ size as nvarchar (20) +' * '+ cast (@ pagecout as nvarchar (20) +''
Set @ sqlpagecountstr = 'select rownumber = ceiling (count (id) * 1.0/'+ cast (@ size as nvarchar (20) +' from (select top 1000 row_number () over (order by '+ @ ordercolmn +') as rownum from ('+ @ sqlstr1 + 'Union all' + @ sqlstr2 +') s) t'

 

---- Total number of returned rows
If (@ pagecout = 0)
Begin
Exec (@ sqlpagecountstr)
End
------ Return the data on the call page
If (@ pagecout> 0)
Begin
Exec (@ sqlstr)
End
------ Return the total number of pages and the first page of data
If (@ pagecout =-1)
Begin
Exec (@ sqlstr)
Set @ pagecout = 1
Set @ sqlpagecountstr = 'select rownumber = ceiling (count (id) * 1.0/'+ cast (@ size as nvarchar (20) +' from (select top 1000 row_number () over (order by '+ @ ordercolmn +') as rownum from ('+ @ sqlstr1 + 'Union all' + @ sqlstr2 +') s) t'
Exec (@ sqlpagecountstr)
End
End