SQL output parameter usage and paging Stored Procedure

Use [capitalfortune]
Go
/***** Object: storedprocedure [DBO]. [setmultipages] script Date: 05/30/2012 10:17:02 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- ===================================================== ======
-- Author: hugejile
-- Alter Date: 2070-9-29
-- Description: pagination. Do not change it at will.
-- ===================================================== ======
Alter procedure [DBO]. [setmultipages]
-- Add the parameters for the stored procedure here
@ Tablename nvarchar (2000 ),
@ Columnnames nvarchar (2000 ),
@ Conditions nvarchar (2000 ),
@ Orderbystring nvarchar (2000 ),
@ Pagesize Int = 1000,
@ Currentpage Int = 1,
@ Recordcount Int = 0 output,
@ Pagecount Int = 1 Output
As
Begin
-- Set nocount on added to prevent extra result sets from
-- Interfering with select statements.
Set nocount on;

Declare @ SQL nvarchar (4000 ),
@ Rowcount int
Set @ rowcount = 0
If @ columnnames is null or Len (@ columnnames) = 0
Begin
Set @ columnnames = '*'
End

If @ currentpage is null
Begin
Set @ currentpage = 1
End
 
If @ pagesize is null
Begin
Set @ pagesize = 10
End
 
If @ conditions is null
Begin
Set @ conditions =''
End
Else
Begin
If Len (@ conditions) <> 0
Begin
Set @ conditions = 'where' + @ Conditions
End
End

-- Insert statements for procedure here
If @ currentpage> 0
Begin
Set @ SQL = n' select * from (select '+ @ columnnames +', (row_number () over (order by '+ @ orderbystring + ')) as rownum from '+ @ tablename + @ conditions +') t where T. rownum between '+ Cast (@ CurrentPage-1) * @ pagesize + 1 as nvarchar (10) +' and '+ Cast (@ currentpage * @ pagesize as nvarchar (10 )) + '; Set @ rowcount = rowcount_big ()'
Print 'select commandtext: '+ @ SQL
Execute sp_executesql @ SQL, n' @ rowcount int output', @ rowcount output

Set @ SQL = 'select @ recordcount = count (*) from' + @ tablename + @ Conditions
Print 'count commandtext: '+ @ SQL
Execute sp_executesql @ SQL, n' @ recordcount int output', @ recordcount output
Set @ pagecount = ceiling (1.0 * @ recordcount/@ pagesize)
Print @ recordcount
-- Return @ recordcount
End
Else if @ pagesize> 0
Begin
Set @ SQL = n'select top '+ Cast (@ pagesize as nvarchar (10) + ''+ @ columnnames +', (row_number () over (order by '+ @ orderbystring +') as rownum from '+ @ tablename + @ conditions + 'ORDER BY' + @ orderbystring
Print 'select commandtext: '+ @ SQL
Exec (@ SQL)
Set @ SQL = 'select @ recordcount = count (*) from' + @ tablename + @ Conditions
Print 'count commandtext: '+ @ SQL
Execute sp_executesql @ SQL, n' @ recordcount int output', @ recordcount output
Set @ pagecount = ceiling (cast (@ recordcount as float)/cast (@ pagesize as float ))
-- Print cast (@ recordcount as nvarchar (10) + 'pagesize' + Cast (@ pagesize as nvarchar (10 ))
-- Print @ pagecount
-- Return @ rowcount
End
Else
Begin
Set @ SQL = n'select' + @ columnnames + ', (row_number () over (order by' + @ orderbystring + ')) as rownum from '+ @ tablename + @ conditions + 'ORDER BY' + @ orderbystring +'; Set @ recordcount = rowcount_big ()'
Print 'select commandtext: '+ @ SQL
-- Exec (@ SQL)
-- Set @ SQL =''
-- Print 'count commandtext: '+ @ SQL
Execute sp_executesql @ SQL, n' @ recordcount int output', @ recordcount output
Set @ pagecount =-1
End
Return 1
End

This is a paging storage process, and there should be no problem with millions of data.

Call Method

Declare @ return_value int,
@ Recordcount int,
@ Pagecount int,
@ Pagesize int

Exec @ return_value = [DBO]. [setmultipages]
@ Tablename = 'users ',
@ Columnnames = '*',
@ Conditions = '',
@ Orderbystring = 'userid ',
@ Pagesize = 10,
@ Currentpage = 0,
@ Recordcount = @ recordcount output,
@ Pagecount = @ pagecount output

Select @ recordcount as N 'recordcount ',
@ Pagecount as n'pagecount ',
@ Pagesize as n' pagesize'

 

@ Recordcount = @ recordcount output,
@ Pagecount = @ pagecount output: these two parameters are output parameters. They are used to accept the values of output parameters in the stored procedure and calculate the total data and total number of pages. When calling, it must be noted that the output parameter mark is the output keyword identifier!