Paging SQL stored procedure

01	set ANSI_NULLS ON

02	set QUOTED_IDENTIFIER ON

03

go

04

05

/*

06	Function Description: Common paging display query

07	Do not add this field to @ strGetFields if you have an auto-increment field,

08	If you want to add it, You Need To (fldName + 0)AS FldName;

09	Input parameters:

10	@ TblName: Table Name

11	@ StrGetFields: column to be returned'*': Returns column information.

12	@ PageSize: page size

13	@ PageIndex: page number

14	@ DoCount: Total number of records returned. If the value is not 0

15	@ StrOrderBy: Sorting field information (Note: Do not addORDER BY)

16	Format: Field1DESC, Field2 ASC

17	@ StrWhere: Query condition (Note: Do not addWHERE)

18	Output parameter: @ RecordCount: Total number of records

19	Author: Nestcn

20	Created on:

21	Change record:

22

*/

23	ALTER PROCEDURE [dbo].[MyPagination]

24

(

25	@tblName varchar(255),

26	@strGetFields varchar(1000) = '*',

27	@PageSize int = 10,

28	@PageIndex int = 1,

29	@doCount bit = 0,

30	@strOrderBy varchar(500) = '',

31	@strWhere varchar(1500) = '',

32	@RecordCount int output

33

)

34

AS

35	-- Subject sentence

36	DECLARE @strSQL varchar(5000) SET @strSQL = ''

37	-- Sort Variables

38	DECLARE @strOrder varchar(400) SET @strOrder = ''

39

40	SET @RecordCount = 0

41	-- If the value passed by @ doCount is not 0, the total number of statistics will be executed.

42	IF (@doCount != 0)

43

BEGIN

44	DECLARE @sWhere varchar(2000)

45

46	SET @sWhere = ''

47	IF (@strWhere != '')

48	SET @sWhere = ' WHERE ' + @strWhere

49

50	SET @strSQL = 'if exists (select * from dbo.sysobjects where id = object_id(''[dbo].[tmpTable]'') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '

51	SET @strSQL = @strSQL + ' UPDATE tmpTable SET Total = (SELECT COUNT(*) FROM [' + @tblName + '] ' + @sWhere + ') '

52	SET @strSQL = @strSQL + ' ELSE SELECT COUNT(*) AS Total INTO tmpTable FROM [' + @tblName + '] ' + @sWhere

53

54	EXEC (@strSQL)

55

56	SELECT @RecordCount=Total FROM tmpTable

57

58	-- Delete the temporary table for total count statistics

59	EXEC ('DROP TABLE tmpTable')

60

END

61

62	PRINT @RecordCount

63

64	-- Sort Field Information

65	IF (@strOrderBy != '')

66	SET @strOrder = ' ORDER BY ' + @strOrderBy

67	-- Execute the above Code on the first page, which will speed up the execution.

68	IF (@PageIndex = 1)

69

BEGIN

70	IF (@strWhere != '')

71	SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder

72

ELSE

73	SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM ['+ @tblName + '] '+ @strOrder

74

END

75

ELSE

76

BEGIN

77	-- Create an automatic number for the search table and save it to the temporary table

78	SET @strSQL = 'SELECT TOP ' + str(@PageIndex*@PageSize) + ' IDENTITY(int,1,1) AS IID, ' + @strGetFields + ' INTO #tmpTable FROM [' + @tblName + ']'

79	IF (@strWhere != '')

80	SET @strSQL = @strSQL + ' WHERE ' + @strWhere + @strOrder

81

ELSE

82	SET @strSQL = @strSQL + @strOrder

83

84	-- The following code gives @ strSQL the SQL code to be actually executed

85	SET @strSQL = @strSQL + ' SELECT ' + @strGetFields + ' FROM #tmpTable WHERE IID > ' + str((@PageIndex-1)*@PageSize) + ' DROP TABLE #tmpTable'

86

END

87

88	PRINT @strSQL

89

90	-- Query by PAGE

91	EXEC (@strSQL)