A SQLProcedure For MSSQL 2005 Of CustomPaging With RowNum And CustomPaging Without RowNum Both For MSSQL 2000 And MSSQL 2005
WITH Clause AND EXEC sp_executesql Clause Used In MSSQL 2005
Introduction
This is a SQLProcedure For MSSQL 2005 Of CustomPaging.
We often use CustomPaging controls which are dependant on a special SQLProcedure we wrote.
In short, it's very useful and effective.
- Why use
WITHclause- Reusable, enables us to create virtual views (which are used for storing the SQL)
- More readable, make the repeated sections centralized and aliased
- More effective, a
WITHclause is calculated only once
- Why use
EXEC sp_executesqlclause- Dynamic, building the SQL will fetch data from different tables
Here is the code for the SQLProcedure.
Final Edition Support For MSSQL 2000
ALTER PROCEDURE dbo.kuuy_P_PagingFinal
@getFields NVARCHAR(500)='', --字段名
@tblName NVARCHAR(255)='', --表名
@strWhere NVARCHAR(1500)='', --筛选语句
@strOrder NVARCHAR(1000)='NewsID',--排序字段
@keyName NVARCHAR(50)='NewsID', --键值
@IsCount BIT=0, --是否计数
@PageSize INT=10, --分页大小
@CurrentPage INT=1, --当前页
@ItemCount INT=0 OUTPUT
AS
DECLARE @strCount NVARCHAR(1000) --Total Records
DECLARE @strSQL NVARCHAR(4000) --SQL语句
DEClARE @strTmp NVARCHAR(2000) --临时语句
DECLARE @tmpTable VARCHAR(10) --临时表名
Set @tmpTable = '#tmp_data'
--条件判定
IF (@strOrder <> '') --排序字段
SET @strOrder = ' ORDER BY '+@strOrder
IF(@strWhere <> '') --筛选字段
SET @strWhere =' WHERE '+@strWhere
IF (@IsCount <> 0)
SET @strCount = ' SET @ItemCount=(SELECT COUNT(' + @keyName + ') _
FROM '+@tblName+@strWhere+')'
ELSE
SET @strCount =''
IF @CurrentPage > 1
BEGIN
--创建临时表
SET @strSQL = 'SELECT IDENTITY(INT,1,1) as RowNum,' + @keyName + _
'*1 as ID INTO '+ @tmpTable
SET @strSQL = @strSQL + ' FROM ' + @tblName + ' T'+ @strWhere + @strOrder
SET @strSQL = @strSQL + ' CREATE UNIQUE CLUSTERED INDEX Idx_UC_RowNum ON ' + _
@tmpTable + '(RowNum)'
SET @strTmp='(SELECT TOP '+ ltrim(str(@PageSize)) + ' ID FROM '+@tmpTable +
' WHERE RowNum > '+ ltrim(str((@CurrentPage-1)*@PageSize)) +
' AND RowNum < '+ ltrim(str(@CurrentPage*@PageSize+1)) +') '
SET @strSQL = @strSQL + ' SELECT ' + @getFields + ' FROM ' + _
@tblName + ' WHERE ' + @keyName + ' IN ' + @strTmp + @strOrder
--删除临时表
SET @strSQL = @strSQL + ' '+' DROP TABLE '+@tmpTable
END
ELSE
SET @strSQL = 'SELECT TOP '+ltrim(str(@PageSize))+' 0 AS RowNum,' + _
@getFields + ' FROM'
+@tblName+@strWhere+@strOrder+@strCount
PRINT @strSQL
EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',@ItemCount OUTPUT
RETURN
CustomPaging SQLProcedure For SQL2005 With RowNum
ALTER PROCEDURE [dbo].[kuuy_P_CustomPaging]
@getFields NVARCHAR(500)='', --Columns_Name
@tblName NVARCHAR(255)='', --Table_Name
@strWhere NVARCHAR(1500)='', --Conditions
@strOrder NVARCHAR(1000)='',--Order
@IsCount BIT=0, --Identity Whether Return The Total Results
@PageSize INT=10, --RowsCount You Want Fetch Once
@CurrentPage INT=1, --CurrentPage
@ItemCount INT=0 OUTPUT
AS
DECLARE @strSQL NVARCHAR(4000) --Dynamic SQL string
DEClARE @strTmp NVARCHAR(2000) --TEMP SQL String
DECLARE @ItemCountValue INT
DECLARE @strCount NVARCHAR(4000) --Total Records
IF (@strOrder <> '')
SET @strOrder = ' ORDER BY '+@strOrder
ELSE
SET @strOrder = ' ORDER BY ID'
IF(@strWhere <> '')
SET @strWhere =' WHERE '+@strWhere
SET @strTmp='WITH T_Orders AS('+
'SELECT ROW_NUMBER() OVER(' + @strOrder + ') _
AS RowNum,' + @getFields + ' ' +
'FROM ' + @tblName + ' ' + @strWhere +
')'
IF (@IsCount!=0)
SET @strCount = ' SET @ItemCount=(SELECT COUNT(*) FROM '+@tblName+@strWhere+');'
ELSE
SET @strCount =''
SET @strSQL = @strCount +
@strTmp + 'SELECT TOP ('+str(@PageSize)+') RowNum,' + _
@getFields + ' ' +
'FROM T_Orders ' +
'WHERE RowNum BETWEEN ' + str((@CurrentPage-1)*_
@PageSize+1) + ' AND '+ str(@CurrentPage*@PageSize)
EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',_
@ItemCount=@ItemCountValue OUTPUT
SET @ItemCount = @ItemCountValue
RETURN
CustomPaging SQLProcedure Without RowNum Both For MSSQL 2000 And MSSQL 2005
ALTER PROCEDURE [dbo].[kuuy_P_CustomPaging_WithoutRowNum]
@getFields NVARCHAR(500)='', --Columns_Name
@tblName NVARCHAR(255)='', --Table_Name
@strWhere NVARCHAR(1500)='', --Conditons
@strOrder NVARCHAR(1000)='',--Order
@keyName NVARCHAR(50)='ID', --KeyName
@oprateKeyValue VARCHAR(2)='>', --Operate
@funKeyValue NVARCHAR(100)='MAX(ID)', --Identity The Order Key value
@IsCount BIT=0, --Identity Whether Return The Total Results
@PageSize INT=10, --RowsCount You Want Fetch Once
@CurrentPage INT=1, --CurrentPage
@ItemCount INT=0 OUTPUT
AS
DECLARE @strSQL NVARCHAR(4000) --Dynamic SQL string
DEClARE @strTmp NVARCHAR(2000) --TEMP SQL String
DECLARE @ItemCountValue INT
DECLARE @strCount NVARCHAR(4000) --Total Records
IF (@strOrder <> '')
SET @strOrder = ' ORDER BY '+@strOrder
ELSE
SET @strOrder = ' ORDER BY ID'
IF(@strWhere <> '')
BEGIN
IF(@CurrentPage > 1)
SET @strWhere =' AND '+@strWhere
ELSE
SET @strWhere =' WHERE '+@strWhere
END
IF (@IsCount <> 0)
SET @strCount = ' SET @ItemCount=(SELECT COUNT(*) FROM '+@tblName+@strWhere+')'
ELSE
SET @strCount =''
IF(@CurrentPage > 1)
BEGIN
SET @strTmp='WITH T_Orders AS(SELECT TOP('+str(@CurrentPage*@PageSize)+')'_
+@keyName+' FROM '+@tblName+@strWhere+@strOrder+')'
SET @strSQL = @strTmp+'SELECT TOP ('+str(@PageSize)+')'+@getFields+' '+
'FROM '+@tblName + ' '+
'WHERE '+ @keyName + @oprateKeyValue +
'(SELECT ' + @funKeyValue + ' FROM (SELECT TOP(' + _
str((@CurrentPage-1)*@PageSize) + ')* FROM T_Orders) AS T)'+
@strWhere+@strOrder
END
ELSE
SET @strSQL = 'SELECT TOP('+str(@PageSize)+')'+@getFields+' _
FROM '+@tblName+@strWhere+@strOrder
EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',_
@ItemCount=@ItemCountValue OUTPUT
PRINT @strSQL
SET @ItemCount = @ItemCountValue
RETURN